CODEWITHSUNDEEP
sql-server
Jiah
Jiah

Reputation: 93

Isnull - Case and conditions on where clause

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?

Upvotes: 1

Views: 75

Answers (2)

DhruvJoshi
DhruvJoshi

Reputation: 17126

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)
            )))

Upvotes: 1

Thom A
Thom A

Reputation: 95554

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)))

Upvotes: 2

Related Questions