CASE Statement And Cursor

Before going to any of the two approach  i need to briefly explain  about CASE statement in SQL SERVER. 
 
CASE STATEMENT  In a real life we are often faced with situation where we are required to make a choice between two diffrents conditions for example if i will choose Computer science branch in B-tech then i would be a computer science Engg,if mechanical branch the i will be a mechanical engg etc.
 
Like this in sql server CASE statement are used to fetch or modify records based on some conditions.For example if the gender status is 'M' we need to fetch records as 'Male' from table else 'Female',etc.
 
For this you can use both Case statement as well as Cursor.But from these two alternative Case statement is the better one.
 
I would have some list of questions to explane this case and Cursor.
 
Here i am Creating a table as 'Employee1' which contain following colums.
 
 

And now i am inserting some Records into the table.
 
 

And Now select the table to view the  contents
 
 
 
Now the question is where the Gender is 'M' update to 'Male and where Gender is 'F' update it to 'Female'.
As i told you we can solve this problem in two ways
  1. By Cursor
  2. By CASE Statement 
Here i am explaining each one of them and showing  there merits and demerits to you.
 
BY USING CURSOR
 
 
 
Here i am briefly explaining  step by step  for creating cursor to archive our goal.
 
Now select the table.

 

Now we get the result as we expected.
 
 

So for this we need to write minimum 6 to 7 lines of query.Now i will achive the same result with in 3 lines by using Case statement as follow.
 
BY USING CASE STATEMENT
 
The synatax for Case Statement is
 
Case Expression

When Expression1 then Result1
When Expression 2 then Result2
else
Result n
end
 
 
 
This is to show the data in required format. To update the data we need update command as follow

 

The else part in case statement is not mendatory. Now we got the result as
 
 
 
 

Thus we got the same result using case statement in 2 to 3 line.That's why we normally prefer Case statement when we get any situation like this.
Ebook Download
View all
Learn
View all