WHERE clause with IN clause with CASE WHEN...

Tapan Shroff

I am trying to use below code and it does not work... but cant live without below conditions... is there any otherway anyone knows to do the same thing?

if i pass 0 in STRID then it should show me ALL records but if i pass 1,2,5 then it should show me only records which has 1,2,5 ID's in tble.

Thanks in Advance..

DECLARE @STRID AS VARCHAR(MAX)
--SET @STRID = '1,2,3'
SET @STRID = NULL

SELECT * FROM STORES
WHERE StrID IN ( CASE WHEN @STRID = '0' THEN @STRID WHEN @STRID <> '0' THEN (SELECT * FROM FN_SPLIT_ID_STR(@STRID,',')) END )

Feb 13 '12 #1

ck9663

First, instead of the second when, use an ELSE.
Second, this
should be
I'm not digging into your function.

Happy Coding!!!


~~ CK

Feb 13 '12 #2

Tapan Shroff

Sorry that was typo, i have added else but the code suggested also did not work for me...

may be i was not clear in description ... i have changed the query little (below)...

what i need is if parameter value is 0(ZERO) then all records should display and if the parameter value is passed as (multiple ID's) then it should display only the selected records...

Please suggest...

Feb 13 '12 #3

ck9663

Use the code tag so it's readable.

I'm sure this can be refactored, but if your table is not that big, it will not matter.

Try this:
Only one of those condition will be true at any given point in time. I considered NULL as '0'. Just change it if necessary.

Happy Coding!!!


~~ CK

Feb 13 '12 #4

Tapan Shroff

It worked like a charm.... Thank You so much..

Feb 14 '12 #5