SQL Server 2012 – Using T-SQL for Queries with IF Condition

sql servert-sql

I have a database with tables Student(StudId, StudName,…) and Grades (GradeExam1, GradeExam2,..) etc. And I want to do a query to assign a letter grade to each grade. I am thinking of doing something like:

SELECT StudId, GradeExam1 AS 'A' IF GradeExam1>= 90 AND GradeExam1 <=100
UNION
SELECT StudID, GradeExam1 AS 'B' ..........

I know that I can use non-conditional aliases within plain SQL, but I have never done conditional aliases within an SQL query.

Or maybe I need T-SQL in order to do a loop for a given exam, instead of doing a union of select statements for different conditions?

Thanks in Advance.

Best Answer

You're talking about a CASE expression:

DECLARE @Table table (StudentId INT, GradeExam1 int, GradeExam2 int);
insert into @Table (StudentId,GradeExam1,GradeExam2) values (1,95,88);
insert into @Table (StudentId,GradeExam1,GradeExam2) values (2,85,93);
insert into @Table (StudentId,GradeExam1,GradeExam2) values (3,75,87);

SELECT StudentId
    ,CASE 
        WHEN GradeExam1 >= 90
            AND GradeExam1 <= 100
            THEN 'A'
        WHEN GradeExam1 >= 80
            AND GradeExam1 <= 90
            THEN 'B'
        WHEN GradeExam1 >= 70
            AND GradeExam1 <= 80
            THEN 'C'
        END AS GradeExam1
    ,CASE 
        WHEN GradeExam2 >= 90
            AND GradeExam2 <= 100
            THEN 'A'
        WHEN GradeExam2 >= 80
            AND GradeExam2 <= 90
            THEN 'B'
        WHEN GradeExam2 >= 70
            AND GradeExam2 <= 80
            THEN 'C'
        END AS GradeExam2
FROM @table;

My example of a CASE expression is Standard SQL and not specific to just the T-SQL syntax.

Check here, if you'd like to learn more about the implementation of the CASE expression.

Related Question