Getting a Error in Switch Statement in SQL

Question

I am using SWITCH Statement in SP under Where Clause.Query Seems to be fine but i am unable to understand why it is giving me the error. Query is:

Select * from Customer
WHERE  (I.IsClose=@ISClose OR @ISClose is NULL)  
AND    
(C.FirstName like '%'+@ClientName+'%' or @ClientName is NULL )    
AND 
 CASE @abc
         WHEN 2 THEN I.RecurringCharge=@Total or @Total is NULL
         WHEN 3 THEN I.RecurringCharge like 
                               '%'+cast(@Total as varchar(50))+'%' 
                     or @Total is NULL 
     END

The code is incomplete but sufficient to understand the problem.I am getting Below Error Message when trying to compile it.

Incorrect syntax near '='.

The Error is on following line.

WHEN 2 THEN I.RecurringCharge=@Total or @Total is NULL

Please help me experts.

Thanks in advance.

The T-SQL CASE statement can only return (atomic) values (like 1, Hello) - not entire code blocks or expressions... — marc_s
– marc_s, Commented Nov 1, 2012 at 7:43
but this is my requirment to return like above based on @abc. Can i fix it somehow ,or there is no solution for it ? — Asp_Newbie
– Asp_Newbie, Commented Nov 1, 2012 at 7:49
I don't understand what you're trying to do - in the middle of your WHERE clause, you're trying to assign variables and stuff..... — marc_s
– marc_s, Commented Nov 1, 2012 at 8:18
possible duplicate of How to use If Statement in Where Clause in SQL? — RichardTheKiwi
– RichardTheKiwi, Commented Nov 1, 2012 at 11:32
@Asp_Newbie I would suggest going back to your unresolved problem and UNMARKING it as accepted. It is going to mislead visitors to the page. Also look at my answer on that question for rephrasing your query. Actually, you should list some rows of data and the expected results to be absolutely clear what logic you are after. — RichardTheKiwi
– RichardTheKiwi, Commented Nov 1, 2012 at 11:33

Cristian Lupascu · Accepted Answer · 2012-11-01 07:52:36Z

2

See the documentation of the CASE sattement.

Instead CASE <variable> WHEN <value> ... you should use CASE WHEN <variable>=<value> ....

In your case, you'd be better off not useng CASE at all. Try rewriting the conditions like this instead

Select * from Customer
WHERE  (I.IsClose=@ISClose OR @ISClose is NULL)  
AND    (C.FirstName like '%'+@ClientName+'%' or @ClientName is NULL )    
AND    (@Total is NULL OR
            (
                (@abc = 2 and I.RecurringCharge=@Total)
                OR
                (@abc = 3 and I.RecurringCharge like '%'+cast(@Total as varchar(50))+'%')
            )
        )

I indented the conditions just to make it clear how they are applied. I also factored out the common OR @Total is NULL condition.

answered Nov 1, 2012 at 7:52

Cristian Lupascu

40.8k18 gold badges107 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting a Error in Switch Statement in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related