SQL Server - IF...ELSE Statement

IF is conditional statement where it check boolean value that true or false.

By using IF..ELSE IF we can set the multiple if conditions like case statement. If condition get satisfied then if block get executed otherwise else part get execute.

Below is the syntax of IF..ELSE.

1) Example

In below example we are checking that if user is above 60 then select user type as senior citizen else select not senior citizen.

Example

Copy

DECLARE @UserAge INT = 65 --Set user agent is 65

IF ( @UserAge >= 60 )
BEGIN
    SELECT 'Use is senior citizen' AS 'UserType'
END
ELSE
BEGIN
    SELECT 'Use is not senior citizen' AS 'UserType'
END

Output

Note : If we have single statements in if..else block then no need to write the BEGIN and END block.

2) Example

Below example indicate multiple if conditions in SQL Server. It also called as IF...ELSE IF... ELSE example.

Example

Copy

DECLARE @UserAge INT = 42 --Set user agent is 42

IF ( @UserAge <= 18 )
BEGIN
    SELECT 'Use age less than 18' AS 'AgeGroup'
END
ELSE IF ( @UserAge <= 40 )
BEGIN
    SELECT 'Use age between 18 and 40' AS 'AgeGroup'
END
ELSE IF ( @UserAge <= 60 )
BEGIN
    SELECT 'Use age between 40 and 60' AS 'AgeGroup'
END
ELSE
BEGIN
    SELECT 'Use age greater than 60' AS 'AgeGroup'
END

Output

3) Example

We can also use multiple conditions in IF or ELSE IF statement.

Consider below result set and we have to find out that Vikrant and Jyo are belong from same city and class or not?

Example

Copy

DECLARE @VikrantClass INT , @VikrantCity VARCHAR (10)= ''
DECLARE @JyoClass INT , @JyoCity VARCHAR (10)= ''

SELECT @VikrantClass = Std, @VikrantCity = City FROM tblStudentMaster WHERE StudentName='Vikrant'
SELECT @JyoClass = Std, @JyoCity = City FROM tblStudentMaster WHERE StudentName='Jyo'

IF ( @VikrantClass = @JyoClass ) AND ( @VikrantCity = @JyoCity )
BEGIN
    SELECT 'Both belong from same std. and city' AS 'Result'
END
ELSE
BEGIN
    SELECT 'Both not belong from same std. and city' AS 'Result'
END

Output

Note : We can concat multiple conditions using AND or OR operators.