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
