Query with CASE in WHERE not working

Question

Hello,

I have a strange problem and I was curious if anyone knows the answer.  I am trying to run the query below in SQL Server Management Studio, in the SQL Pane ("Through Edit Top 200 Rows" off the table object explorer) and for some reason I get the error "The parameter is incorrect".  The problem seems to be with my @OFFICE_CD variable because if I hard code the int value it works fine.  I have tried Casting @OFFICE_CD to int and I still get the same error.

I am stumped!

Thanks!

SELECT [PERSON_LAST_NAME],
       [PERSON_FIRST_NAME],
       [OFFICE_CD] ,
       [PERSON_OFFICE] ,
       [PERSON_PHONE]
FROM [vw_PERSON] 
WHERE ([PERSON_OFFICE] = CASE @OFFICE_CD WHEN 2147483647 THEN [PERSON_OFFICE] ELSE @OFFICE_CD END)

Answer 1

I assume that you did declare the @OFFICE_CD parameter, but your comment about casting indicates that the parameter is not an INT.  Can you declare @OFFICE_CD as an INT parameter? 

Also @OFFICE_CD and the column [PERSON_OFFICE] should be the same data type.  A column can only have one datatype in it.

Your CASE looks OK to me, but an alternative form of CASE is:

CASE
     WHEN @OFFICE_CD = 2147483647 THEN [PERSON_OFFICE]
     ELSE @OFFICE_CD
END

RLF

Answer 2

Thanks Russell for your help!

Yep, I declared the parameter as int.  The reason I mentioned about casting was because I was desperate and I was trying everything I could think of and it still wasn't working. :)

What I ended up doing was using a like statement in my where clause rather than the case:

WHERE [PERSON_OFFICE] LIKE @OFFICE_CD

and passed in a % in the parameter when I wanted all records.  

I didn't like having to do it that way but that was the only way I could get it to work.

Thanks again Russell!