How to use Switch in SQL Server

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.

2

4 Answers

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

4

This is a select statement, so each branch of the case must return something. If you want to perform actions, just use an if.

 select @selectoneCount = case @Temp when 1 then (@selectoneCount+1) when 2 then (@selectoneCount+1) end select @selectoneCount 

1

Actually i am getting return value from a another sp into @temp and then it @temp =1 then i want to inc the count of @SelectoneCount by 1 and so on. Please let me know what is the correct syntax.

What's wrong with:

IF @Temp = 1 --Or @Temp = 2 also?
BEGIN SET @SelectoneCount = @SelectoneCount + 1
END

(Although this does reek of being procedural code - not usually the best way to use SQL)

3

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy