Introduction

Case statement acts like an If-Else statement in stored procedure. It will modify the name of the data, which is not actual data in the table.

Description

Case statement is mostly used in real time scenarios. Sometimes it is based on Id of a table's column value. You can mention the list of names in the dropdown binding list using case statement in T-SQL.

Steps to follow

Create one table.
  1. CREATE TABLE tblDepartment1
  2. (
  3. DeptId int Primary Key,
  4. DeptName nvarchar(20)
  5. )
Insert some contribution records in this table.
  1. Insert into tblDepartment1 values (1,'Blog')
  2. Insert into tblDepartment1 values (2,'Article')
  3. Insert into tblDepartment1 values (3,'Resource')
  4. Insert into tblDepartment1 values (4,'Book')
Script to create a stored procedure is given to implement case statement.
  1. Create procedure Sp_CaseStatement
  2. As
  3. Begin
  4. set NoCount ON;
  5. select case tbldepartment1.deptname when 'Blog' Then 'Blog(400)'
  6. when 'Article' Then 'Article(100)'
  7. when 'Resource' Then 'Resource(200)'
  8. when 'Book' Then 'Book(300)'
  9. else 'UnKnown' end as 'Your Contribution Counts'
  10. from tbldepartment1
  11. end
Run the table with select statement.
  1. Select * from tblDepartment1
Now, run stored procedure.
  1. exec Sp_CaseStatement
See the differences that stored procedure will not affect the real data to the table's records. It just shows to the users.


Summary
  1. What is case statement, using stored procedure?
  2. How to implement, using stored procedure in real time scenario.