SQL Variables for T-SQL Code and Queries

Problem

Using local variables is not a new topic or approach in writing SQL queries. However, most individuals I train or interact with on this topic seem unaware of how to use variables in writing efficient and dynamic SQL queries.

In T-SQL, a local variable is described as an object that can hold a scalar value (single data value) of a specific data type. To learn more about what a T-SQL local variable is used for, continue to read the Microsoft documentation: Variables (Transact-SQL).

This tutorial will describe how to use these local variables in different ways in SQL statements.

Solution

There are a few points to note while using local variables in T-SQL:

Local variables are used to hold single data values within a batch execution period.
Local variables can hold data of different types.
Local variables cannot be used in a SQL view.
You can assign values to a local variable.
The scope of a variable is limited to a single query; it cannot be reused in another SQL query.
The scope of a local variable, when applied to a stored procedure, is limited to the current stored procedure only.

How Do I Declare a T-SQL Local Variable?

The syntax for a T-SQL local variable can differ slightly depending on whether you use it in SQL Server, Azure SQL Database, Azure Synapse Analytics, or Microsoft Fabric.

The summarized syntax for a T-SQL local variable is seen below. However, note that the syntax below can get more complex with additional arguments, but it depends on what you are using the variable for. See this Microsoft documentation for this. For this introduction, the variable declaration syntax below is sufficient:

DECLARE { @local_variable [AS] data_type [ = value ] }

The SQL variable syntax above requires the following:

@local_variable: Provide a variable name, which must start with an “@” sign.
data_type: Define the data type (int, char, varchar, decimal, numeric, datetime, etc.) for the declared variable. You cannot assign the data types to be a “text“, “ntext“, or “image” types.
= value: This is optional, as you can set a variable value in another way. But when used, it assigns the initial value to the declared variable. You can either “hard-code” this value or use an expression. In both cases, the value must either be an exact match for the data type of the declared variable or can be convertible to it implicitly.

Let’s now look at an example of how to declare a local variable in T-SQL. In the example below, a variable named @SaleYr was declared and assigned a datatype of VARCHAR.

DECLARE @SaleYr VARCHAR(5);

The above variable will default as NULL since we will not assign a value to the variable until later.

What if we have more than one variable to declare? The code below shows how this can be done.

DECLARE @SaleYr VARCHAR(5);
DECLARE @pk VARCHAR(30);

Or you could do this:

DECLARE @SaleYr VARCHAR(5),
        @pk VARCHAR(30);

But the difference, as you can see, is that both the first and second option ends each DECLARE statement with a semi-colon (“;”), while the second option separates the two DECLARE statements with a comma (“,”) and ends the DECLARE statement with a semi-colon at the end of the last variable.

Note: You can add as many variables as you want, but you must follow the logic of the first or second option above.

Since we are yet to assign a value to the declared variables, the output at this time would be initialized to NULL (mentioned earlier) and should appear like the image below.

Output message to show a declared T-SQL variable has been created.

How Do I Assign a Value to a T-SQL Local Variable?

T-SQL has different options to assign a value to a declared variable. I will demonstrate three options next.

Option 1:

Immediately assign a value to the variable as it is created.

DECLARE @SaleYr VARCHAR(5) = 2013;
DECLARE @pk VARCHAR(30) = 480;

DECLARE @SaleYr VARCHAR(5) = 2013,
        @pk VARCHAR(30) = 480;

Option 2:

Use the SET statement.

DECLARE @SaleYr VARCHAR(5);
DECLARE @pk VARCHAR(30);

SET @SaleYr = 2013;
SET @pk = 480;

DECLARE @SaleYr VARCHAR(5),
        @pk VARCHAR(30);
 
SET @SaleYr = 2013;
SET @pk = 480;

Note: A separate SET statement has been used to assign a value for each local variable declared, unlike the second option of the DECLARE statement we described earlier, where we can use a single DECLARE statement for more than one variable.

Option 3:

Use the SELECT statement to assign values to the declared variable(s).

DECLARE @SaleYr VARCHAR(5);
DECLARE @pk VARCHAR(30);

SELECT @SaleYr = 2013;
SELECT @pk = 480;

DECLARE @SaleYr VARCHAR(5),
        @pk VARCHAR(30);
 
SELECT @SaleYr = 2013;
SELECT @pk = 480;

We can add a PRINT statement to view the output of the declared variables and the assigned values:

DECLARE @SaleYr VARCHAR(5),
        @pk VARCHAR(30);
 
SELECT @SaleYr = 2013;
SELECT @pk = 480;
 
PRINT @SaleYr
PRINT @pk

The output in SQL Server is shown in the image below:

Output of a declared T-SQL variable displayed using PRINT command.

How Do I Dynamically Assign a Value to a T-SQL Local Variable from a Table?

All examples we have looked at have been assigning values using “hard-coded” scalar values. T-SQL also allows a dynamic SQL expression to assign scalar values to a variable.

The example below uses the “FactInternetSales” table in the AdventureWorks2012 database. As you can see, I have used an expression to retrieve the MAX year from the “OrderDateKey” of the table. If you run this expression on its own, it should return a single value of the maximum year in the OrderDate column. Remember, it must return a scalar value; otherwise, it won’t work.

DECLARE @SaleYr VARCHAR(5);
 
SET @SaleYr = (SELECT MAX(LEFT(OrderDateKey,4)) FROM [AdventureWorksDW2012].[dbo].[FactInternetSales]);
 
SELECT [ProductKey]
      ,LEFT([OrderDateKey],4) Year
      ,[SalesOrderNumber]
      ,[SalesOrderLineNumber]
      ,[OrderQuantity]
  FROM [AdventureWorksDW2012].[dbo].[FactInternetSales] fis
  WHERE LEFT([OrderDateKey],4) = @SaleYr

The output of the above query is shown below.

Result of a T-SQL variable used in a simple query.

You can also reference a variable within a value assignment within the same batch of code, as seen in the following example:

DECLARE @SaleYr VARCHAR(5);
DECLARE @PrevSaleYr VARCHAR(5);
 
SET @SaleYr = (SELECT MAX(LEFT(OrderDateKey,4)) FROM [AdventureWorksDW2012].[dbo].[FactInternetSales]);
SET @PrevSaleYr =  @SaleYr - 1;
 
SELECT [ProductKey]
      ,LEFT([OrderDateKey],4) Year
      ,[SalesOrderNumber]
      ,[SalesOrderLineNumber]
      ,[OrderQuantity]
  FROM [AdventureWorksDW2012].[dbo].[FactInternetSales] fis
  WHERE LEFT([OrderDateKey],4) = @PrevSaleYr

Above, I have declared a second variable, “@PrevSaleYr“, and used it in setting a new value by subtracting one from it to give me a dynamic previous year. So instead of the MAX year 2014, I will have 2013, as seen in the image below.

Result of a T-SQL variable used in a simple query 2

In summary, this article has shown how to leverage T-SQL local variables in a SQL query. There are advanced use cases of this topic that you should check out. If you are interested in learning more, please see the “Next Steps” in this article to follow links to read more. However, this article is expected to introduce you to the concept of using variables to help you write or manage your T-SQL scripts efficiently.

Next Steps

Resources

Community

Subscribe