T-SQL is the query language you will be using to interact with SQL Server database. Here ‘T’ stands for Transact.

Variables in SQL Server

There are two types of variables like most other programming languages:
Local Variable: It is user-defined and starts with a single @ sign.
Global Variable: It is system defined and starts with @@ sign.

Declaring a variable

You can declare one or multiple variables using below format.

DECLARE { @LOCAL_VARIABLE[AS] data_type [ = value ] }
DECLARE @ID AS INT;
DECLARE @NAME VARCHAR (10);
DECLARE @ID AS INT, @NAME VARCHAR (10);

Please note, it is not mandatory to initialize a variable when declaring it. Also, ‘AS’ keyword is optional.

You can initialize or set value for a variable using SET or SELECT statement in one of the following valid ways:

DECLARE @Local_Variable <data_type>
SET @Local_Variable = <value>

DECLARE @Local_Variable _1 <data_type>, @Local_Variable_2 <data_type>,
SET @Local_Variable_1 = <value1>
SET @Local_Variable_2 = <value2>

DECLARE @Local_Variable_1 <data_type>, @Local_Variable_2 <data_type>,SET @Local_Variable_1 = (SELECT <column_name> from <table_name> where <condition> )

DECLARE @LOCAL_VARIABLE <data_type>
SELECT @LOCAL_VARIABLE = <value>

DECLARE @ID as INT, @NAME AS VARCHAR(5)
SELECT @ID = 1, @NAME = ‘RAVI’
PRINT @ID
PRINT @NAME

DECLARE @NAME VARCHAR (10)
SELECT @NAME = (select first_name from customers where customer_id = 1)
PRINT @NAME

DECLARE @NAME VARCHAR (10)
SELECT @NAME = first_name from customers where customer_id = 3
PRINT @NAME

Please note:

  • If query returns no rows, the value is set to NULL.
  • If query returns multiple rows, the value is set to value in the last row.
  • SET can assign value to only one variable at a time, however, you can use SELECT to set values to multiple variables in same line separated by commas.
  • A local variable can be displayed using PRINT as well as SELECT command.

IF … ELSE Conditional statements

The If .. Else statement is pretty standard across programming languages. Below are examples of some valid IF .. ELSE statements in SQL Server.

IF (1=1)
PRINT ‘IF STATEMENT: CONDITION IS TRUE’
ELSE
PRINT ‘ELSE STATEMENT: CONDITION IS FALSE’

DECLARE @ID INT = 2
IF (@ID <=2)
BEGIN
Select * from table where ID = 1
Select * from table where ID = 2
END
ELSE
BEGIN
Select * from table where ID = 3
Select * from table where ID = 4
END

DECLARE @ID INT = 2
IF (@ID <=2)
Select * from table where ID = 2

DECLARE @age INT;
SET @age = 60;

IF @age < 18
PRINT ‘underage’;
ELSE
BEGIN
IF @age < 50
PRINT ‘You are below 50’;
ELSE
PRINT ‘Senior’;
END;

CASE Statement

A CASE statement is an extension of IF .. ELSE statement. Below are some valid usage of CASE statement in SQL Server.

SELECT DEPT_ID, DEPT_NAME,
CASE DEPT_NAME
WHEN ‘ACT’ THEN ‘Accounts Department’
WHEN ‘ADM’ THEN ‘Administrative Department’
WHEN ‘ACD’ THEN ‘Academic Department’
END AS Description
FROM DEPARTMENT

SELECT DEPT_ID, DEPT_NAME,
CASE DEPT_NAME
WHEN ‘ACT’ THEN ‘Accounts Department’
WHEN ‘ADM’ THEN ‘Administrative Department’
WHEN ‘ACD’ THEN ‘Academic Department’
ELSE ‘This is a new department.’
END AS Description
FROM DEPARTMENT

SELECT DEPT_ID, DEPT_NAME,
CASE
WHEN DEPT_NAME = ‘ACT’ THEN ‘Accounts Department’
WHEN DEPT_NAME = ‘ADM’ THEN ‘Administrative Department’
WHEN DEPT_NAME = ‘ACD’ THEN ‘Academic Department’
END AS Description
FROM DEPARTMENT

SELECT DEPT_ID, DEPT_NAME,
CASE
WHEN DEPT_NAME = ‘ACT’ THEN ‘Accounts Department’
WHEN DEPT_NAME = ‘ADM’ THEN ‘Administrative Department’
WHEN DEPT_NAME = ‘ACD’ THEN ‘Academic Department’
ELSE ‘This is a new department.’
END AS Description
FROM DEPARTMENT

UPDATE DEPARTMENT
SET DEPT_NAME =
(
CASE
WHEN DEPT_NAME = ‘ACT’ THEN ‘Structured Query language.’
WHEN DEPT_NAME = ‘ADM’ THEN ‘Oracle PL/SQL’
WHEN DEPT_NAME = ‘MSSQL’ THEN ‘Microsoft SQL.’
END
)

Declare @Order Int;
Set @Order = 1
Select * from DEPARTMENT order by
CASE
WHEN @Order = 1 THEN DEPT_ID
WHEN @Order = 2 THEN DEPT_NAME
END
DESC