Getting error with CASE expression using WHERE clause

Question

declare @SP nvarchar(3)

SELECT HDR.SOPNUMBE, HDR.docdate, HDR.pymtrmid, 
RTRIM(HDR.ShipToName) +
CASE WHEN HDR.ADDRESS1 <> '' THEN char(10) + RTRIM(HDR.ADDRESS1) ELSE '' END 
FROM vSOPHdr HDR
WHERE HDR.SOPTYPE = 2 AND 
(CASE WHEN HDR.SLPRSNID <> 'All' 
then HDR.SLPRSNID in (''+@SP+'') 
else HDR.SLPRSNID between '0' and 'ZZZZZZZZZZZZZZZ'  
end)

I am trying to run the above query in sql query analyzer but getting 'syntax' errors in the CASE expression (near IN, ELSE). What could be wrong?

All I want to do is when sales-personID is selected as ALL in the report parameter I would like to process all sales person else only the selected sales person ID.

Just a background about what I am doing,

I have a similar query to the above query (without the CASE condition) within a dataset for a SSRS report.

Note that @SP is a report parameter.

Answer 1

CASE is an expression that returns a single value. It cannot be used for control-of-flow logic, like in other languages such as VB. Try:

WHERE HDR.SOPTYPE = 2 AND 
((HDR.SLPRSNID <> 'All' AND HDR.SLPRSNID = @SP)
OR
(HDR.SLPRSNID = 'All'))

Though it seems like maybe you need instead, which would make more logical sense if the user is potentially passing 'All' into the parameter:

WHERE HDR.SOPTYPE = 2 AND 
((@SP <> 'All' AND HDR.SLPRSNID = @SP)
OR
(@SP = 'All'))

Answer 2

you can just use an OR clause

WHERE HDR.SOPTYPE = 2 AND 
((HDR.SLPRSNID <> 'All' AND HDR.SLPRSNID IN (''+@SP+'')) 
OR 
(HDR.SLPRSNID = 'All' AND HDR.SLPRSNID BETWEEN '0' and 'ZZZZZZZZZZ'))

Not sure if the BETWEEN '0' and 'ZZZZZZZZZZZ' means "IN everything". If yes, you can just remove it now.

If you absolutely wanna use a CASE WHEN, you can do it this way :

WHERE HDR.SOPTYPE =2 
AND (CASE WHEN HDR.SLPRSNID <>'All' AND HRD.SLPRSNDID IN (''+@SP+'')) THEN 1
          WHEN HDR.SLPRSNID = 'All' AND HDR.SLPRSNID BETWEEN '0' and 'ZZZZZZZZZZ' THEN 1
          ELSE 0 
     END) = 1