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 [email protected] 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 [email protected] 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 @[email protected]+1 END GO
You get the following stored procedure output for the local variable as a counter.
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: March 25, 2022