Conditionally include a WHERE clause

[Hexonx]

In my app, I pass in a CSV list of IDs to include in processing and store them in a temp table. I then include a conditional on the WHERE clause as such:

WHERE Amount > 0
AND ID IN ( SELECT ID FROM #tblIDs )

Is there a way to do something like this:

WHERE Amount > 0
IF LEN( @IDList ) > 0
AND ID IN ( SELECT ID FROM #tblIDs )
ELSE
-- No filtering on ID; include all

The only way I can think of is to build a query string in a VARCHAR and EXEC it, but this is grossly inefficient.

 

[tlbroadbent]

How about trying something like this?

WHERE Amount > 0
AND ((LEN(@IDList)>0
AND ID IN (SELECT ID FROM #tblIDs))
OR (LEN(@IDList)=0))
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[TomSark]

I'm missing something here, but I'm guessing that you want to join the ID in a real table to the ID in a temp table, you could try:

IF EXISTS (
SELECT R.ID
FROM RealTable R
INNER JOIN #tblIDs T ON R.ID = T.ID
AND R.Amount > 0
)
BEGIN
SELECT R.ID
FROM RealTable R
INNER JOIN #tblIDs T ON R.ID = T.ID
AND R.Amount > 0
END
ELSE
BEGIN
SELECT whatever
FROM wherever
END

If my guess isn't right (that you can have an ID in the real table that you can join to the temp table), let me know and I'll give you a different solution.
Tom Davis
tdavis@sark.com

http://www.sarkdallas.com