IF ELSE and CASE in SQL Server

SQL Server has no SWITCH keyword.

IF ... ELSE -> choose which statement to run
CASE        -> works like switch, gives a value inside a query
IIF         -> short IF for one condition

This lesson creates its own small tables, so you can run it on any SQL Server database. The Output under each query is the real result.

IF DB_ID('SqlBasicsDb') IS NULL
    CREATE DATABASE SqlBasicsDb;
GO

USE SqlBasicsDb;
GO

DROP TABLE IF EXISTS StudentMarks;
GO

CREATE TABLE StudentMarks
(
    StudentID INT,
    Name      VARCHAR(50),
    Marks     INT,
    DayNo     INT          -- 1 = Monday ... 7 = Sunday
);

INSERT INTO StudentMarks VALUES
(1, 'Ravi',  85, 1),
(2, 'Sita',  92, 3),
(3, 'John',  45, 5),
(4, 'Meena', 67, 6),
(5, 'Arjun', 30, 7);
GO

-- =============================================
-- PART 1 : VARIABLES (needed for IF ELSE)
-- =============================================

DECLARE @Name VARCHAR(50) = 'Ravi';
DECLARE @Age INT;
SET @Age = 20;

SELECT @Name AS Name, @Age AS Age;
PRINT 'Name is ' + @Name;           -- PRINT shows text in the Messages tab
GO

-- =============================================
-- PART 2 : IF ELSE
-- =============================================

(5 rows affected)

Name is Ravi

Simple IF

DECLARE @Marks INT = 75;

IF @Marks >= 35
    PRINT 'Pass';
GO

Pass

IF ELSE

DECLARE @Marks INT = 20;

IF @Marks >= 35
    PRINT 'Pass';
ELSE
    PRINT 'Fail';
GO

Fail

IF ... ELSE IF ... ELSE

DECLARE @Marks INT = 82;

IF @Marks >= 90
    PRINT 'Grade A';
ELSE IF @Marks >= 75
    PRINT 'Grade B';
ELSE IF @Marks >= 35
    PRINT 'Grade C';
ELSE
    PRINT 'Fail';
GO

Grade B

More than one statement -> use BEGIN ... END

DECLARE @Age INT = 19;

IF @Age >= 18
BEGIN
    PRINT 'Adult';
    PRINT 'Can vote';
END
ELSE
BEGIN
    PRINT 'Minor';
    PRINT 'Cannot vote';
END
GO

Adult
Can vote

IF with data from a table

DECLARE @Count INT;
SELECT @Count = COUNT(*) FROM StudentMarks WHERE Marks < 35;

IF @Count > 0
    PRINT 'Some students failed: ' + CAST(@Count AS VARCHAR(10));
ELSE
    PRINT 'Everyone passed';
GO

Some students failed: 1

IF EXISTS - check if a row exists

IF EXISTS (SELECT 1 FROM StudentMarks WHERE Name = 'Sita')
    PRINT 'Sita found';
ELSE
    PRINT 'Sita not found';
GO

-- =============================================
-- PART 3 : CASE (works like SWITCH)
-- =============================================

Sita found

Simple CASE - compare one value (exactly like switch)

SELECT Name, DayNo,
       CASE DayNo
           WHEN 1 THEN 'Monday'
           WHEN 2 THEN 'Tuesday'
           WHEN 3 THEN 'Wednesday'
           WHEN 4 THEN 'Thursday'
           WHEN 5 THEN 'Friday'
           WHEN 6 THEN 'Saturday'
           WHEN 7 THEN 'Sunday'
           ELSE 'Unknown'
       END AS DayName
FROM StudentMarks;

Searched CASE - use conditions

SELECT Name, Marks,
       CASE
           WHEN Marks >= 90 THEN 'A'
           WHEN Marks >= 75 THEN 'B'
           WHEN Marks >= 35 THEN 'C'
           ELSE 'Fail'
       END AS Grade
FROM StudentMarks;

CASE with a variable

DECLARE @Color VARCHAR(10) = 'G';

SELECT CASE @Color
           WHEN 'R' THEN 'Red'
           WHEN 'G' THEN 'Green'
           WHEN 'B' THEN 'Blue'
           ELSE 'Other'
       END AS ColorName;
GO

CASE in ORDER BY - show failed students first

SELECT Name, Marks
FROM StudentMarks
ORDER BY CASE WHEN Marks < 35 THEN 0 ELSE 1 END, Name;

CASE inside UPDATE

ALTER TABLE StudentMarks ADD Result VARCHAR(10);
GO

UPDATE StudentMarks
SET Result = CASE WHEN Marks >= 35 THEN 'Pass' ELSE 'Fail' END;

SELECT * FROM StudentMarks;

-- =============================================
-- PART 4 : IIF (short IF, one condition)
-- =============================================

(5 rows affected)

IIF(condition, value if true, value if false)

SELECT Name, Marks, IIF(Marks >= 35, 'Pass', 'Fail') AS Result
FROM StudentMarks;

-- =============================================
-- PART 5 : WHILE loop (bonus)
-- =============================================

Print numbers 1 to 5

DECLARE @i INT = 1;

WHILE @i <= 5
BEGIN
    PRINT @i;
    SET @i = @i + 1;
END
GO

1
2
3
4
5