How to use Switch in SQL Server

Question

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.

Answer 1

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