Stored procedure that accepts multiple id values (where parameter accepts null)

Question

This question would be addendum on the last answer in T-SQL stored procedure that accepts multiple Id values

I am passing a few list of ids as a parameter to a stored procedure. Each of them default to null if no data is sent in. For instance, I want food products with ids 1, 2, 5, 7, 20 returned by my stored procedure. I also send in a list of color ids, and production location ids. I am passing in a comma delimited list of these ids. Similar to the last answer in the question referenced above, I create a temp table with the data from each of the parameters. I then want to have a select statement that would be something like this:

SELECT * FROM Candies 
INNER JOIN #TempColors 
    ON Candies.ColorsID = #TempColors.ColorID 
INNER JOIN Locations 
    ON Candies.LocationID = Locations.LocationID

This only works when the parameters are populated and LEFT OUTER JOINS will not filter properly. What is the way to filter while accepting null as a valid parameter?

Charleh Charleh · Accepted Answer · 2012-07-09T19:50:51

You could use

some join condition OR @param IS NULL

in your join, it would return all results if a null was supplied - though as far as I can see you don't specify what behaviour you want when null is passed

(when I say param I mean temp table column doing this on my phone and it's not easy ;))

Edit:

This one worked for me:

http://sqlfiddle.com/#!3/c7e85/26

e.g.

-- Assume this is your values string which is populating the table
DECLARE @Values varchar(50)

-- Your code to populate the table here: assume the string is NULL when no values are passed
INSERT INTO #TempColors BLAH BLAH...

-- Select statement
SELECT * FROM Candies  
    LEFT JOIN #TempColors  
        ON Candies.ColorsID = #TempColors.ColorID
WHERE 1 = CASE 
              WHEN Candies.ColorsID IS NULL AND @Values IS NULL THEN 1
              WHEN Candies.ColorsID IS NOT NULL AND @Values IS NOT NULL THEN 1
              ELSE 0
          END

This way the NULLs will be filtered out with a NON-NULL parameter, but kept in for a NULL parameter

Stored procedure that accepts multiple id values (where parameter accepts null)

1 Answers