TSQL Optional Parameters that work on the same field

Question

I have a stored procedure where I pass in a product type (prod_type) as a parameter up to four times. Potentially each of these parameters may be null, in which case they should be ignored. They should have a OR operator between each product type so we collect all of the product types requested.

If possible, I want to avoid using dynamic SQL passed though sp_ExecuteSQL (I know it would be easy through this).

A simplified, isolated version of what I am trying to do is below for discussion:

CREATE TABLE #Products (Prod_ID int, prod_type int);

INSERT INTO #Products
SELECT 1, 2
UNION ALL
SELECT 2, 3
UNION ALL
SELECT 3, 3
UNION ALL
SELECT 4, 1
UNION ALL
SELECT 4, 5

DECLARE @prod_type1 as int;
DECLARE @prod_type2 as int;
DECLARE @prod_type3 as int;
DECLARE @prod_type4 as int;

SET @prod_type1 = NULL;
SET @prod_type2 = 2;
SET @prod_type3 = NULL;
SET @prod_type4 = 3;

Select * from #Products
WHERE ((prod_type = ISNULL(@prod_type1,prod_type)) 
OR (prod_type = ISNULL(@prod_type2,prod_type)) 
OR (prod_type = ISNULL(@prod_type3,prod_type) 
OR (prod_type = ISNULL(@prod_type4,prod_type))))

Where what I would like to be executed is something like:

Select * from [Product].[Product]
WHERE (prod_type = 2 OR prod_type = 3)

Clearly the above workaround with ISNULL will not work, as the ISNULL method will compare the product type of each row to itself which will result in a positive 'hit'. COALESCE will have the same problem.

Can anyone suggest a solution that doesn't involve the use of sp_ExecuteSQL?

Answer 1

Try changing you Where Clause like this.

Select * 
from 
#Products
WHERE prod_type in(@prod_type1,@prod_type2,@prod_type2,@prod_type4)

Answer 2

Try this:

DECLARE @ProdTypes TABLE (Prod_Type INT NOT NULL);

INSERT INTO @ProdTypes (Prod_Type) VALUES (2);
INSERT INTO @ProdTypes (Prod_Type) VALUES (3);

SELECT pr.*
FROM #Products pr
WHERE EXISTS (SELECT 1
              FROM @ProdTypes pt
              WHERE pt.Prod_Type = pr.Prod_Type);

Using the EXISTS allows for what is logically an OR condition. And the EXISTS will exit if a match is found so it won't run through all rows in @ProdTypes unless:

• There are no matching values
• The matching value is the last one in the set ;-)

But many times it will exit before scanning the whole table variable.

EDIT:
Also, you shouldn't pass in multiple @Prod_TypeX parameters to your procedure. Instead, pass in a CSV list of Ints and use a SQLCLR- or XML- based string splitter to insert that into the table variable, as in:

INSERT INTO @ProdTypes (Prod_Type)
   SELECT CONVERT(INT, csv.SplitVal)
   FROM   dbo.SQLCLRorXMLbasedSplitter(@ProdTypesCSV) csv;