The Basics of SQL Server Variables

By: Aubrey Love   |   Updated: 2022-12-21   |   Comments (2)   |   Related: > TSQL

Problem

I see many people using SQL variables in Microsoft SQL Server, but haven't fully grasped the concept with the T-SQL language. In this tutorial, we take a look at declaring and using variables in SQL statements.

Solution

Let's start at the beginning with the understanding that SQL variables are not created, but rather, they are declared. Yes, there is a difference. To create means that you have created an object in SQL Server that will remain after you close your SQL instance. When you declare a variable in SQL Server, it's a temporary object and will only be usable during the script execution. SQL variables don't exist as an entity but as an object value inside the script.

SQL Variable Declaration

As mentioned earlier, we "declare" variables in SQL Server and assign them a datatype and value in a SQL query. We can later use that value to return a result set. Hence, we actually use the word "DECLARE" when setting a SQL variable and must use an @ symbol preceding the variable name.

The basic syntax to declare a variable, the data type and the variable value is as follows:

DECLARE @LOCAL_VARIABLE data_type = value of the variable

We can also declare a variable first and use a set statement to define its value later:

DECLARE @LOCAL_VARIABLE data_type

SET @LOCAL_VARIABLE = value

An excellent point to remember is that variables are just as they sound, something that varies. This means that you can change the value of a variable at any time. However, you cannot change the datatype or name of a variable.

We will create a variable in the code sample below, set its value to 12 and run a SELECT script calling that variable. Next, we will re-assign the value of that variable and again run a SELECT script to see the difference.

DECLARE @LOCAL_VARIABLE INT
 
SET @LOCAL_VARIABLE = 12
SELECT 12 * @LOCAL_VARIABLE AS 'var * 12'
 
SET @LOCAL_VARIABLE = 14
SELECT 12 + @LOCAL_VARIABLE AS 'var + 14';
GO

Query results:

Assigning Values to a SQL Variable

Transact-SQL has two options are available to assign a value to a variable: set and select. We used the SET command in the previous section to assign a value. This section will use the SELECT statement to assign our values. We can select a string value or pull a value from a table to use as the value of our variables.

In this sample, we assign a string value to a variable that we will call "testvalue1":

DECLARE @testvalue1 VARCHAR(20)

SELECT @testvalue1 = 'Hello World!'

PRINT @testvalue1;
GO

Query results:

In the following example, we are using a value in the "FirstName" column of the Person.Person table in the AdventureWorks2019 sample database. You can get a free copy of the sample database here.

In our sample code, we treat it like any other SELECT statement, but our desired value is being called by referencing the value of our variable. In other words, we assign our variable the first name in the Person.Person table that has a BusinessEntityID of 8.

USE AdventureWorks2019;
GO

DECLARE @testvalue2 AS VARCHAR(20)

SELECT @testvalue2 = [FirstName]
FROM Person.Person
WHERE BusinessEntityID = 8

PRINT @testvalue2;
GO

Query results:

You can also declare multiple variables at the same time. Below, we will expand on the previous sample code by adding the "LastName" column value from our Person.Person table.

USE AdventureWorks2019;
GO

DECLARE @testvalue2 AS VARCHAR(20)
DECLARE @testvalue3 AS VARCHAR(20)

SELECT 
   @testvalue2 = [FirstName]
 , @testvalue3 = [LastName]
FROM Person.Person
WHERE BusinessEntityID = 8

PRINT @testvalue2
PRINT @testvalue3;
GO

Query results:

We can also assign a value to a SQL variable from a scalar-valued function.

DECLARE @testvalue3 AS INT

SELECT @testvalue3 = dbo.ufnGetStock(3)

SELECT @testvalue3 AS [MyValue];
GO

Query results:

It's important to remember that variables are only available during a query execution. If a batch is terminated with the "GO" batch separator, then you can no longer reference the variable.

Example: Referring to our original sample, if we terminate the batch and then try to print the value of our variable, we will get an error.

DECLARE @testvalue1 VARCHAR(20)
SELECT @testvalue1 = 'Hello World!'
PRINT @testvalue1;
GO

PRINT @testvalue1;

Query results:

As you can see from the result set above, the print statement inside the batch ran successfully, but the print statement outside the batch did not. That's because the variable no longer exists once the batch has been terminated.

DECLARE, DECLARE, DECLARE. Can I clean up the code if I need to declare more than one variable in a batch? Yes, there is. When declaring variables and setting their values, you can comma separate them without having to state DECLARE or SET for each variable.

In the sample below, we will declare two variables with one DECLARE statement, set the two values with one SELECT statement, and print both values with one PRINT statement.

USE AdventureWorks2019;
GO

DECLARE @testvalue2 AS VARCHAR(20), @testvalue3 AS VARCHAR(20)

SELECT
    @testvalue2 = [FirstName]
  , @testvalue3 = [LastName]
FROM Person.Person
WHERE BusinessEntityID = 8 -- WHERE Clause

PRINT @testvalue2 + ' ' + @testvalue3;GGO

Query results:

Wrapping Up

In this tutorial, we looked at some of the fundamentals of SQL variables. These are the basic building blocks that every DBA and database developer needs to start with when learning about SQL variables. We learned what a variable is and is not, how to create one or more variables, and how to use those variables to our advantage.

Next Steps

Click the links below to continue reading about SQL variables:

• SQL Server INSERT command with variables
• SQL Variables in Scripts, Functions, Stored Procedures, SQLCMD and More
• SQL Variables for Queries and Stored Procedures in SQL Server, Oracle and PostgreSQL
• Some Tricky Situations When Working with SQL Server NULL

About the author

Aubrey Love is a self-taught DBA with more than six years of experience designing, creating, and monitoring SQL Server databases as a DBA/Business Intelligence Specialist. Certificates include MCSA, A+, Linux+, and Google Map Tools with 40+ years in the computer industry. Aubrey first started working on PC’s when they were introduced to the public in the late 70's.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips