An Overview of the SQL Variables Usage

The SQL variable is a local variable that can store a single data value temporarily. Its lifecycle starts with declaration and ends with the batch. Similarly, if you use the local variable in a stored procedure, its scope is within the SP execution.

The few typical usages of the SQL variables are as below.

• Single point declaration: You can declare the variable and call that variable in the T-SQL script many times(within scope). It can store a default value and override the value during the runtime.
• Counter: Many times, you require a counter to execute the script in a loop. The SQL variable can be used to store and update the counter value with the loop execution.
• Save or return value: The variable can store the stored procedure return code or Output value.

How to Declare a SQL Variable

To declare a variable in a T-SQL script, use the following approach.

• Use the DECLARE statement for variable declaration
• Specify a unique name for the variable in the scope of the T-SQL statement. You cannot have two variables with the same name in a script, and the variable name must specify a single @ as the first character.
• Specify the data type for the value that will be stored in the variable.
• You can assign a default value for the variable during declaration as well. However, it is optional to assign a value during declaration.

For example, the following T-SQL script declares a variable and prints its value.

DECLARE @Temp varchar(10) = 'SQL Server'
PRINT @Temp

If we want to assign a value after the variable declaration, we can use the SET statement with the variable name below.

DECLARE @Temp varchar(10) 
SET @Temp = 'SQL Server'
PRINT @Temp

Alternatively, the SELECT statement is mainly used to assign local variable values. You can assign the static value or assign the value to the select statement output.

DECLARE @Temp varchar(10) 
Select @Temp = 'SQL Server'
PRINT @Temp

For example, in the below T-SQL, the variable stores the column [CompanyName] value returned by the SELECT statement.

DECLARE @Temp varchar(50) 
Select @Temp = [CompanyName] 
FROM  SalesLT.Customer WHERE CustomerID=1
PRINT @Temp

If the SELECT statement returns the multiple rows, the variable gets a value returned for the last row of the result set. For example, In the below script, we removed where predicates. Therefore, the SELECT statement returns all rows stored in the [SalesLT].[Customer] table.

DECLARE @Temp varchar(50) 
Select @Temp = [CompanyName] 
FROM  SalesLT.Customer 
PRINT @Temp

The script works fine. However, it returns the value of the last row of the result set.

We can declare multiple local variables in a SQL script. We can either use multiple DECLARE statements or a single one with a comma-separated statement to declare the multiple variables.

DECLARE @a int = 1
DECLARE @b int = 2
DECLARE @c varchar(10) = 'Test'
PRINT @a
Print @b
Print @c

Alternatively, the below SQL statement declares all variables using a single DECLARE statement.

DECLARE @a int = 1 , @b int = 2 , @c varchar(10) = 'Test'
PRINT @a
Print @b
Print @c

Scope of the Local Variable

As specified earlier, the local variable scope is in the batch itself. Let’s have a test on this. The below script works fine as we used the variable in a single batch.

DECLARE @CustID INT;
SET @CustID = 1;
SELECT * FROM salesLT.Customer 
WHERE CustomerID= @CustID

SQL Server uses the GO operator to segregate the different batches. Therefore, the following script separates the variable declaration from the SELECT statement.

DECLARE @CustID INT;
SET @CustID = 1;
go
SELECT * FROM salesLT.Customer 
WHERE CustomerID= @CustID

The SELECT statement generates a syntax error because the variable @CustID is no longer valid.

The local variable scope is within the batch or procedure where they are defined. The following script also generates the error due to the nested scope in the sp_executesql.

DECLARE @Temp INT;
SET @Temp = 1;
EXECUTE sp_executesql N'SELECT @Temp';

Variables in the Stored Procedure

The stored procedure can contain local variables as a parameter or be embedded into the T-SQL script. For example, in the below-stored procedure, we used the local variable @ProductID.

CREATE PROCEDURE [dbo].[GetProductNumber]
(@ProductID AS int)
AS
BEGIN
    SELECT ProductNumber FROM SalesLT.Product
    WHERE ProductID = @ProductID
END
GO

To execute the SP, we need to specify the value of variable @ProductID as below.

In another example, we declare another local variable @ProductCategoryID inside the body of the stored procedure. Once we execute the SP with specifying a value of the variable @ProductID, it fetches the value of @ProductCategoryID using the first SELECT statement. It filters records into the second SELECT statement as per the value assigned to the local variable @ProductCategoryID.

CREATE OR ALTER PROCEDURE [dbo].[GetProductNumber]
(@ProductID AS int)
AS
BEGIN
DECLARE @ProductCategoryID int
    SELECT @ProductCategoryID= ProductCategoryID FROM SalesLT.Product
    WHERE ProductID = @ProductID
	SELECT Name, Color, StandardCost FROM SalesLT.Product
	WHERE ProductCategoryID=@ProductCategoryID
END
GO

The local variable scope is in the script batch. We can use it for nested batches also. For example, in the below script, we use the @ProductCategoryID variable inside another loop.

CREATE OR ALTER PROCEDURE [dbo].[GetProductNumber]
(@ProductID AS int)
AS
BEGIN
DECLARE @ProductCategoryID int
    SELECT @ProductCategoryID= ProductCategoryID FROM SalesLT.Product
    WHERE ProductID = @ProductID
	BEGIN
	SELECT Name, Color, StandardCost FROM SalesLT.Product
	WHERE ProductCategoryID=@ProductCategoryID
	END
END

The variable inside the stored procedure is not available for other transactions even if you call stored procedure and variable in the same transaction.

Create OR alter procedure TestSP
as 
declare @ID int 
set @ID = 1
print @ID
GO

Now, if we call the variable outside the SP, it generates the error even if we have not separated transactions with the GO statement.

Local Variable as the Counter in T-SQL Script

As stated in the beginning, the local variables can define loop conditions in SQL Server. Suppose you want to execute the code multiple times embedded in a logic. In this case, we can assign a value to the local variable and start the loop by modifying the variable value in each execution.

For example, we defined a variable @ProductID and assigned value 707. Later, I started a WHILE loop that runs until the Product ID is less than 710.

For each execution, it prints the product name and increments the value of the variable by 1.

CREATE OR ALTER PROCEDURE [dbo].[GetProductNumber]
AS
DECLARE @ProductID varchar(10) =707
while(@ProductID<710)
BEGIN 
    SELECT Name FROM SalesLT.Product
    WHERE ProductID = @ProductID
	PRINT 'Product ID ' + @ProductID  + 'details are'
	SET @ProductID=@ProductID+1
END
GO

You get the following stored procedure output for the local variable as a counter.

Conclusion

This article explored the use of local variables and their scope in SQL Server statements. The local variables are used quite often, and therefore, I recommend you be familiar with them in the T-SQL script. To get help with SQL coding, you can use an SSMS add-in dbForge SQL Complete.

Last modified: December 15, 2022