Case Statement Using Stored Procedure In Real Time Scenarios


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.


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.

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