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 )

[ck9663]

First, instead of the second when, use an ELSE.
Second, this

CASE WHEN @STRID = '0' THEN @STRID
 

should be

CASE WHEN @STRID = '0' THEN STRID
 

I'm not digging into your function.

Happy Coding!!!


~~ CK

[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...

DECLARE @STRID AS VARCHAR(MAX)
SET @STRID = '1,2,3'
--SET @STRID = '0'
 
SELECT * FROM STORES 
WHERE (CASE WHEN @STRID = '0' THEN @STRID ELSE Store_ID END)  
IN  
( CASE WHEN @STRID = '0' THEN @STRID ELSE (SELECT *

[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:

SELECT * FROM STORES 
WHERE 
   (isnull(@STRID,'0') = '0' and store_id = stored_id) or
   (isnull(@STRID,'0') <> '0' and store_id in (SELECT * FROM FN_PLGBA_SPLIT_CSV(@STRID,',')))
 

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

[Tapan Shroff]

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