How to use switch case statement in stored procedure?

As easily we use the if else statement in stored procedure to check the statement type and there after we perform the desired task. Same thing I want to do using switch case 

Asked by:- VivekVishwas
: 16163 At:- 8/31/2017 7:37:37 PM
Switch case in sql procedure Switch case SQL

3 Answers
profileImage Answered by:- jaiprakash

There is no stored procedure posted in the above question description so considering this, your stored procedure can be

Create Proc Usp_SampleProcedure
@InParam Int
Set Nocount On

Select Case @InParam When 1 Then 'One'
When 2 Then 'Two'
When 3 Then 'Three'
Else '...'
End [Result]

But I would like to advise., sql Case can be applied to one column and you can't put a query/statement within both "then" and "else" clause, Consider reading these article

I hope this helps, thanks

At:- 9/1/2017 7:27:14 AM

profileImage Answered by:- vikas_jk

Here is the more explanation on SQL SWITCH case expression, Syntax for a simple Switch case statement is

CASE expression

  WHEN data1 THEN output1

  WHEN data2 THEN output1




  WHEN dataN THEN outputN

 ELSE defaultStatement 

Not only we can have an expression in SWITCH CASE but also a series of a boolean expression. See the sample below 


  WHEN boolExpression1 THEN output1

  WHEN boolExpression2 THEN output1



  WHEN boolExpressionN THEN outputN

 ELSE defaultStatement

Example of the CASE statement

SELECT player_name,
       CASE WHEN year = 'SR' THEN 'yes'
            ELSE NULL END AS is_a_senior
  FROM benn.college_football_players

here’s the explanation of above example:

  1. The CASE statement checks each row to see if the conditional statement—year = 'SR' is true.
  2. For any given row, if that conditional statement is true, the word “yes” gets printed in the column that we have named is_a_senior.
  3. In any row for which the conditional statement is false, nothing happens in that row, leaving a null value in the is_a_senior column.
  4. At the same time all this is happening, SQL is retrieving and displaying all the values in the player_name and year columns.

Refer these websites:

Here is the article based on SQL server stored procedure Understanding SQL server switch case (With Example) you can read it, it has all the details.

At:- 9/1/2017 8:02:04 AM Updated at:- 9/1/2017 8:02:21 AM

profileImage Answered by:- pika

Switch case simple example in SQL Server

       THEN (SELECT id FROM someTable) 
       ELSE (SELECT id FROM otherTable)

Above code get id from table "someTable" if x1 = 1, else from table "otherTable'.

At:- 1/24/2022 9:29:45 AM

Login/Register to answer
Register directly by posting answer/details

Full Name *

Email *

By posting your answer you agree on privacy policy & terms of use

Subscribe Now

Subscribe to our weekly Newsletter & Keep getting latest article/questions in your inbox weekly