none
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)
    Tuesday, January 01, 2013 10:27 PM

Answers

  • 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

    Wednesday, January 02, 2013 2:34 AM
  • 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!

    Wednesday, January 02, 2013 11:32 PM

All replies

  • 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

    Wednesday, January 02, 2013 2:34 AM
  • 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!

    Wednesday, January 02, 2013 11:32 PM