One of the key features of SQL Server that helps in managing and manipulating data efficiently is the use of variables. Variables in SQL Server are used to store data temporarily during the execution of code. They are essential in writing reusable, readable, and modular code. By using variables, developers can write more flexible and dynamic SQL queries, enhancing the capability to handle data dynamically during runtime. Understanding how to declare, initialize, and manipulate variables is fundamental for anyone looking to master SQL Server programming.
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. Variables are particularly useful for temporarily storing data, which can be manipulated or transferred as needed within batches, stored procedures, or scripts.
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.
The scope of a local variable is limited to the batch or stored procedure in which it is declared.
Once the batch or procedure ends, the variable is de-allocated and ceases to exist.
Global variables. They are declared by the system beforehand and start with the '@@' symbol.
Global variables can store session information.
As these are system-defined, their lifecycle and scope are managed by SQL Server itself, and they persist across different batches and sessions.
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
Variables are a fundamental aspect of SQL Server programming, enabling developers to write more efficient, dynamic, and readable SQL scripts. Understanding how to properly declare, initialize, and manage variables can significantly enhance your database operations. Practice is key to mastering their use, so experimenting with different scenarios and applications will build your proficiency and confidence in handling variables effectively in SQL Server.