Getting error with CASE expression using WHERE clause
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.
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'))
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