Menu
I want to use CASE in my stored procedure. I am getting some syntax error in my code:
select case @Temp when 1 then (@selectoneCount=@selectoneCount+1) when 2 then (@selectoneCount=@selectoneCount+1) end When running, I'm getting:
incorrect syntax near '='.
at this line here:
@selectoneCount = @selectoneCount + 1 near the equal.
Actually I am getting return value from a another sp into @temp and then if @temp =1 then I want to increment the count of @SelectoneCount by 1 and so on. Please let me know what is the correct syntax.
526
@RamSingh - there's no switch statement in the SQL language. As others have indicated, you can use a CASE expression, but it has to compute and return a scalar value.
It evaluates a list of conditions and returns one of the multiple possible result expressions. You can use CASE expressions anywhere in the SQL Query like CASE expressions can be used within SELECT statement, WHERE clauses, Order by clause, HAVING clauses, & in statements such as SELECT, UPDATE, DELETE and SET.
CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.
The CASE is just a "switch" to return a value - not to execute a whole code block.
You need to change your code to something like this:
SELECT @selectoneCount = CASE @Temp WHEN 1 THEN @selectoneCount + 1 WHEN 2 THEN @selectoneCount + 1 END If @temp is set to none of those values (1 or 2), then you'll get back a NULL
135
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
