SQL - CASE expression inside WHERE

Question

I read about using the CASE expression inside the WHERE clause here:

http://scottelkin.com/sql/using-a-case-statement-in-a-sql-where-clause/

I'm trying to use this to filter results from my select statement, based on a contract number which will be passed in by the user's application. My code currently throws an error of 'Invalid parameter' no matter what is passed in. I verified SELECT/FROM are working fine, as where as a WHERE clause without a CASE expression. Here is my code.

WHERE     (CASE WHEN @ContractNo = 0 THEN @ContractNo ELSE @ContractNo END =
tblContracts.ContractNo)

The redundancy of the code is for troubleshooting purposes, I'm planning on using wildcard filtering on the CASE later. I'm focusing on getting the syntax down right now. I believe this should return all records for which the parameter matches the contract number stored in the table. Any help or advice would be greatly appreciated.

Thanks,

-A humble neophyte

Answer 1

Try leaving out the parentheses which are in the wrong place anyway - the right one should be after "END".

Answer 2

Move your close parenthesis to before the = like so:

WHERE     (CASE WHEN @ContractNo = 0 THEN @ContractNo ELSE @ContractNo END)=tblContracts.ContractNo

I fail to see what this case statement will do though... you're returning the same thing in the event the @ContractNo = 0 or if it's not...

The correct syntax is:

  Select...
  ...
  Where(
    Case
      When <Condition>
        Then <Return if true>
        Else <Return if false>
      End
 ) = <Whatever is being matched to the output of the case statement>

Regardless of the syntax though, your example doesn't make a lot of sense, if you're looking for all items that match or have a Contract Number of 0, then you would do:

Select...
...
Where (
  @ContractNo = 0 Or
  @ContractNo = tblContracts.ContractNo
)

Which seems to make far more sense than what you're attempting to use the case statement for.

Edit: I must've misread the question slightly - the missing param usually means that the parameter (in this case @ContractNo) is not declared in the scope of your query/procedure. But someone already pointed that out, so I can't take any credit for that.

Answer 3

Don't you mean something like this?

SELECT * 
    FROM tblContracts
    WHERE     
    CASE 
       WHEN tblContracts.ContractNo = 0 THEN @ContractNo 
       ELSE tblContracts.ContractNo
    END = tblContracts.ContractNo

Where @ContractNo is variable of the same datatype as tblContracts.ContractNo

Answer 4

Are you sure you want to do this? Your case statement ALWAYS returns @ContractNo. I think what you are looking for is this:

where case @ContractNo when 0 then tblContracts.ContractNo else @ContractNo end = tblContracts.ContractNo

The filter above says "give me the contract where the ContractNo equals the parameter, or all of them if the parameter is 0".

The previous filter only filtered where the contract number field is exactly equal to the parameter.

Regardless, you should do this instead:

where @ContractNo = 0 or @ContractNo = tblContracts.ContractNo

The logic is much easier to understand, and on top of that (don't quote me on this), the optimizer probably will work better outside of the case statement.

Answer 5

Why do you even need a case statement?

WHen @ContractNo = 0 then ( 0 = tblContracts.ContractNo) else @ContractNo then (@ContractNo = tblContracts.ContractNo)

This makes no sense since you could simply write this as

Where @contractNo = tblContracts.contractNo

Answer 6

Maybe you forgot to declare @ContractNo? Is it comparable to 0 and to tblContracts.ContractNo?

Answer 7

Is the contract number actually a numeric or is it a string that always happens to be a numeric. Check your data types between the table and the parameter and the CASE statement (for example, "= 0" or "= '0'")

Answer 8

The reason for the case statement, including the whole "If it's 0, give the parameter, and otherwise, just give the parameter" was to test it to try to get the syntax right. Originally, I had tried saying "If it's 0, then pass in '%', to return every value. The code I posted in there was because I kept getting 'Invalid Parameter' and figured there must be something wrong with my syntax. When I separated it into basic parameter matching like so,

WHERE @ContractNo = tblContracts.ContractNo

it returned records fine. Let me explain a bit more.

I'm pulling from a bunch of different tables, and filtering the content with information not included in the select statement (i.e. tblContracts is not having information pulled from it by Select, it's only used in Where). The user will select from a combo box which will have the different contract numbers, as well as a default value of 'All'.

I'm going to have an event for when the index of the combo box changes. If it's 'All', 0 will be passed in as a parameter and I want no filtering done. Otherwise, I just want the information for that contract number (the reason for Else @ContractNo).

Answer 9

After reading your explanation, there's a better way to do this without CASE:

WHERE @ContractNo = 0 OR tblContracts.ContractNo = @ContractNo

This will return only matching contract numbers, unless @ContractNo is 0, in which case it will return all records.

Edit: I've just noticed that CasperOne proposed the same thing. I didn't see that. Big up yourself.

Answer 10

Recursive's post solved my issue precisely.

I saw complaints about the clarity of my original post. In the future, what can I do to make what I'm saying more straight forward? I'm not used to phrasing questions about code, and apologize for any muddled things it had. Did I just need to provide the extended details like in my 2nd post?

Thanks again for all the help.

Answer 11

This syntax should work (it does in Oracle)

WHERE CASE WHEN tblContracts.ContractNo = 0 
           THEN @ContractNo 
           ELSE tblContracts.ContractNo
      END = tblContracts.ContractNo

Answer 12

for additional consideration: http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx

Answer 13

when you say:

I'm pulling from a bunch of different tables, and filtering the content with information not included in the select statement (i.e. tblContracts is not having information pulled from it by Select, it's only used in Where). The user will select from a combo box which will have the different contract numbers, as well as a default value of 'All'.

Then it sounds to me that should have an "Where exists" clause. since your not pulling any info out of that table?!