[sql-server] 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.

This question is related to sql-server switch-statement syntax-error

The answer is


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)


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 

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to switch-statement

Switch in Laravel 5 - Blade Switch case: can I use a range instead of a one number SQL use CASE statement in WHERE IN clause SSRS Conditional Formatting Switch or IIF Switch statement equivalent in Windows batch file OR operator in switch-case? Regarding Java switch statements - using return and omitting breaks in each case Using two values for one switch case statement C# how to use enum with switch Switch statement multiple cases in JavaScript

Examples related to syntax-error

Uncaught SyntaxError: Unexpected token u in JSON at position 0 How to solve SyntaxError on autogenerated manage.py? Checking whether the pip is installed? (unicode error) 'unicodeescape' codec can't decode bytes in position 2-3: truncated \UXXXXXXXX escape How can you print multiple variables inside a string using printf? Unexpected token < in first line of HTML Laravel: Error [PDOException]: Could not Find Driver in PostgreSQL 'Syntax Error: invalid syntax' for no apparent reason How can I fix MySQL error #1064? Notice: Trying to get property of non-object error