In SQL Server, variables are used to store and manipulate data within a T-SQL script or a stored procedure. Variables provide a way to store temporary values that can be used in various parts of a script or procedure. They can hold different data types, such as integers, strings, dates, or other SQL Server data types.
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. Conclusion
SQL Server variables play a crucial role in writing dynamic and flexible T-SQL scripts. They provide a way to store and manipulate data, making it possible to create more versatile and reusable code. However, it's essential to use variables wisely and be mindful of their scope to avoid potential issues in larger and more complex scripts or procedures.
