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
1
: 324 At:- 8/31/2017 7:37:37 PM
Switch case in sql procedure Switch case SQL






2 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
)As
Begin
Set Nocount On

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

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

https://blog.sqlauthority.com/2007/04/14/sql-server-case-statementexpression-examples-and-explanation/

https://www.codeproject.com/Articles/39103/A-Simple-Use-of-SQL-CASE-Expression

I hope this helps, thanks

1
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 

CASE

  WHEN boolExpression1 THEN output1

  WHEN boolExpression2 THEN output1

  .

  .

  WHEN boolExpressionN THEN outputN

 ELSE defaultStatement

Example of the CASE statement

SELECT player_name,
       year,
       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:
1. https://community.modeanalytics.com/sql/tutorial/sql-case/ 
2.https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql

 

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





Login/Register to answer
Or
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