CASE Expression in SQL Server 2005

SQL CASE Expression can be used effectively and efficiently with SELECT, UPDATE, DELETE Command or with WHERE, HAVING Clause. Here, I am going to show the use of CASE with SELECT Command.

CREATE TABLE t_Education
(
EducationID int IDENTITY(1,1) NOT NULL,
ApplicantID int NOT NULL,
ExamName nvarchar(20) NULL,
MajorName nvarchar(20) NULL,
ResultCode smallint NULL,
CGPA float NULL,
OutOfCGPA float NULL
)

INSERT INTO t_Education(ApplicantID,ExamName,MajorName,ResultCode,CGPA,OutOfCGPA)
VALUES(1,’School Certificate’,’Science’,1,0,0)

INSERT INTO t_Education(ApplicantID,ExamName,MajorName,ResultCode,CGPA,OutOfCGPA)
VALUES(1,’College Certificate’,’Science’,2,0,0)

INSERT INTO t_Education(ApplicantID,ExamName,MajorName,ResultCode,CGPA,OutOfCGPA)
VALUES(1,’Graduation’,’Computer Science’,3,3.31,4)

SELECT ExamName,MajorName,ResultCode,CGPA,OutOfCGPA FROM t_Education

Output:

Result for without CASE Expression

ResultCode field in t_Education table expects only one of following values.

1 => ‘First Class’
2 => ‘Second Class’
3 => ‘Grade’

However, for the last one of those values, user will insert values for CGPA and OutOfCGPA as well. Let us discover effectiveness of CASE Expression to format the output.

SELECT ExamName,MajorName,
CASE ResultCode
WHEN 1 THEN ‘First Class’
WHEN 2 THEN ‘Second Class’
WHEN 3 THEN CAST(CGPA as nvarchar(4)) +’ Out of ‘ + CAST(OutOfCGPA as nvarchar(4))
END
AS “Result”
FROM t_Education

Output:

Result with CASE Expression

Last but not least, using CASE Expression in SQL Query is always appreciated as it reduces execution time and resource consumption. For instance, you can generate this above formatted output using CURSOR, don’t even think of it. You should not use Cursor until on unless all other alternatives fail!!!!

Thanks
A Rahim Khan

Advertisements
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: