T-SQL Tutorial

T-SQL Variables - Declare and Set variable


1. What is a Variable?

A Transact-SQL local variable is an database object that can store a single data value of a specific type.

2. What are the types of variables?

The following are the two main types of SQL Server variables:
Local variables. They are declared by the user and start with the '@' symbol. Local variables can be used within a procedure or batch.
Global variables. They are declared by the system beforehand and start with the '@@' symbol. Global variables can store session information.

3. Declare a Transact-SQL Variable

To declare a variable uses the keyword DECLARE, assign a variable name and a data type. The DECLARE statement of Transact-SQL will declare a variable as per the instruction given by the user.

Syntax for the DECLARE statement:
DECLARE @MyVariable datatype;

The following are the rules behind the DECLARE statement:
A name should be assigned by having '@' before itself.
A data type and length should be assigned. The most used date types are: INT, DATE, VARCHAR.
Initially, the value of the variable is set to null.

Examples:
DECLARE @EMP_ID INT;
DECLARE @EMP_ID AS INT;
DECLARE @EMP_NAME VARCHAR (50);
DECLARE @EMP_ID AS INT, @EMP_NAME VARCHAR (50);

4. Set a Variable Value

After a variable is declared, it gets the default NULL value.
To assign a value to a variable, use the SET statement.
Syntax for the SET statement to set one variable:
DECLARE @Local_Variable Data_Type
SET @Local_Variable = Value


Example:
DECLARE @EMP_ID AS INT
SET @EMP_ID = 5
PRINT @EMP_ID

Syntax for the SET statement to set multiple variables:
DECLARE
@Local_Variable_1 Data_Type,
@Local_Variable_2 Data_Type
SET @Local_Variable_1 = Value
SET @Local_Variable_2 = Value


Example:
DECLARE
@EMP_ID as INT,
@EMP_NAME AS VARCHAR(50)
SET @EMP_ID = 5
SET @EMP_NAME = 'STEVE'
PRINT @EMP_ID
PRINT @EMP_NAME

5. SELECT a Variable

The SELECT statement can be used to select the assigned values by certain criteria as per the requirement of the user. Syntax for the SELECT statement of one variable or multiple variables:
DECLARE @Local_Variable Data_Type
SET @Local_Variable = Value


Example:
DECLARE
@EMP_ID as INT,
@EMP_NAME AS VARCHAR(50)
SELECT @EMP_ID = 5, @EMP_NAME = 'STEVE'
PRINT @EMP_ID
PRINT @EMP_NAME





6. Complex example

USE model;
GO
DECLARE @count int;
DECLARE @name varchar(250);
DECLARE @dsp varchar(250);
SET @count=0;
SET @dsp='Basic of T-SQL';
select @count = count(*) from Certifications;
IF @count > 0
    BEGIN
       select @name=name
       from Certifications
       where description=@dsp;
       PRINT @name
    END;
ELSE
    BEGIN
       PRINT 'Empty table'
    END;
GO

Result:

T-SQL certification

7. More examples