Isnull - Case and conditions on where clause

Question

I have a code snippet where I have a bunch of 'where' conditions merged with 'and' clause. I want to do the below operation:

...     
     and admission_date > release_date
     and case when @sample = 'typeA'
            then 
            (
            (isnull(@toll_id, 0) > 0 and exists(select fk_id from dbo.tollitem where toll_id = @toll_id and isdel=0))
            or 
            (isnull(@toll_id, 0) = 0)
            )

So I have a parameter as @Sample which has two values - Type A or Type B. I want to check the condition on where clause only when @sample = typeA. Above is my code and I am getting incorrect syntax error. Any help?

Post the error message you're getting. And you're missing the END statement for the CASE. — pmbAustin, Apr 02 '18 at 18:03
oops sorry. I got incorrect syntax error near '>' Else part is just select '1'. I don't have specific code for else part — Jiah, Apr 02 '18 at 18:05
Possible duplicate of [Conditional WHERE clause in SQL Server](https://stackoverflow.com/questions/18629132/conditional-where-clause-in-sql-server) — Tab Alleman, Apr 02 '18 at 18:10

Thom A · Answer 1 · 2018-04-02T18:17:53.247

2

That's not how a CASE expression works. In the WHERE clause you still need to make a boolean expression. Thus:

WHERE CASE WHEN Col1 = 1 THEN Col2 = 2 OR Col3 = 3 ELSE Col4 = 1 END;

Is completely wrong, the CASE only completes one side of the expression, and is also trying to evaluate Boolean expressions inside the WHEN. A WHEN returns a value, not a boolean. A correct use of CASE would be (unrelated to above example):

WHERE CASE TransactionType WHEN 'Transfered' THEN 'New Business'
                           WHEN 'Adjustment' THEN 'Endorsement'
                           ELSE TransactionType END = @Trantype;

This is a bit of a shot in the dark, but I think what you're after is:

AND admission_date > release_date
AND (@sample = 'typeA'
AND  ((isnull(@toll_id, 0) > 0 AND EXISTS(SELECT fk_id
                                          FROM dbo.tollitem
                                          WHERE toll_id = @toll_id
                                            AND isdel=0))
 OR  (isnull(@toll_id, 0) = 0)))

edited Apr 02 '18 at 18:17

answered Apr 02 '18 at 18:09

Thom A

88,727
11
45
75

Larnu can you edit the code to show @sample = typeB ? – Jiah Apr 03 '18 at 13:37
@Jiah I'm pretty sure you can figure how to do that one yourself. The **volunteers** on SO aren't here to support the code they provide you (and changing the value of a literal string is extremely simple). It's your job to make sure you understand it and can support it yourself. if you don't understand the code your provided, you can ask about it, but if you still don't, then I more strongly suggest you don't use it. – Thom A Apr 03 '18 at 13:46
totally agree!! I understood the solution given and edited my code to fit my requirement but forgot to update here :) Thanks for the support – Jiah Apr 03 '18 at 14:27

DhruvJoshi · Accepted Answer · 2018-04-02T19:26:03.563

1

You should definitely rewrite this as :

and admission_date > release_date
     and  
       (@sample = 'typeA' and
            (((isnull(@toll_id, 0) > 0 and exists(select fk_id from dbo.tollitem where toll_id = @toll_id and isdel=0))
            or 
            (isnull(@toll_id, 0) = 0)
            )))

edited Apr 02 '18 at 19:26

answered Apr 02 '18 at 18:06

DhruvJoshi

17,041
6
41
60

This code helped me. But there is one more parenthesis needed at the end if I'm not wrong. – Jiah Apr 02 '18 at 18:11
@Jiah define "more than needed". Sometimes they are places for clarity but aren't needed--yet doesn't hurt anything. – S3S Apr 02 '18 at 18:40

Isnull - Case and conditions on where clause

2 Answers2