1

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?

Community
  • 1
  • 1
webdev
  • 33
  • 4

1 Answers1

1

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

Charleh
  • 13,749
  • 3
  • 37
  • 57
  • When I try an inner join on some condition or temp table column IS NULL - it does not return any results when the temp table column is null. I need some code that would accomplish something like - 'only do the inner join to begin with if the @param is not null'. – webdev Jul 10 '12 at 06:28
  • What do you want it to do though? You don't seem to mention anywhere exactly what you want the query to do when you use NULL - you also say **LEFT OUTER JOINS don't filter properly** what does this mean? – Charleh Jul 10 '12 at 09:00
  • When the param is null, I would like the query to return all rows. What I mean by LEFT OUTER JOINS don't filter properly is that they do return all rows when the param is null, however when the param is populated it still returns all row where I want rows with only that (ie. locationid, colorid) – webdev Jul 10 '12 at 09:53
  • I'm guessing you are processing the values into the #temp table in this procedure? Why not just check the param for NULL and populate the temp table with all possible values in that case? – Charleh Jul 10 '12 at 10:26
  • Yes it works. Thank you very much. Your previous idea of populating the temp table with all possible values appeals to me as well, but doesn't work in this case since the fields(ie. Candies.ColorId) are allowed to have nulls. If it were a non-null field that idea would work. I accepted the answer but cannot yet upvote since this is my first question ever on stackoverflow. – webdev Jul 10 '12 at 18:44