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
- By Cursor
- 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.