sql assignment value

Home » SQL Server Stored Procedures » Variables

Summary : in this tutorial, you will learn about variables including declaring variables, setting their values, and assigning value fields of a record to variables.

What is a variable

A variable is an object that holds a single value of a specific type e.g., integer , date , or varying character string .

We typically use variables in the following cases:

• As a loop counter to count the number of times a loop is performed.
• To hold a value to be tested by a control-of-flow statement such as WHILE .
• To store the value returned by a stored procedure or a function

Declaring a variable

To declare a variable, you use the DECLARE statement. For example, the following statement declares a variable named @model_year :

The DECLARE statement initializes a variable by assigning it a name and a data type. The variable name must start with the @ sign. In this example, the data type of the @model_year variable is SMALLINT .

By default, when a variable is declared, its value is set to NULL .

Between the variable name and data type, you can use the optional AS keyword as follows:

To declare multiple variables, you separate variables by commas:

Assigning a value to a variable

To assign a value to a variable, you use the SET statement. For example, the following statement assigns 2018 to the @model_year variable:

Using variables in a query

The following SELECT statement uses the  @model_year variable in the WHERE clause to find the products of a specific model year:

Now, you can put everything together and execute the following code block to get a list of products whose model year is 2018:

Note that to execute the code, you click the Execute button as shown in the following picture:

The following picture shows the output:

Storing query result in a variable

The following steps describe how to store the query result in a variable:

First, declare a variable named @product_count with the integer data type:

Second, use the SET statement to assign the query’s result set to the variable:

Third, output the content of the @product_count variable:

Or you can use the PRINT statement to print out the content of a variable:

The output in the messages tab is as follows:

To hide the number of rows affected messages, you use the following statement:

Selecting a record into variables

The following steps illustrate how to declare two variables, assign a record to them, and output the contents of the variables:

First, declare variables that hold the product name and list price:

Second, assign the column names to the corresponding variables:

Third, output the content of the variables:

Accumulating values into a variable

The following stored procedure takes one parameter and returns a list of products as a string:

In this stored procedure:

• First, we declared a variable named @product_list with varying character string type and set its value to blank.
• Second, we selected the product name list from the products table based on the input @model_year . In the select list, we accumulated the product names to the @product_list variable. Note that the CHAR(10) returns the line feed character.
• Third, we used the PRINT statement to print out the product list.

The following statement executes the uspGetProductList stored procedure:

The following picture shows the partial output:

In this tutorial, you have learned about variables including declaring variables, setting their values, and assigning value fields of a record to the variables.

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

SELECT @local_variable (Transact-SQL)

• 14 contributors

Sets a local variable to the value of an expression.

For assigning variables, we recommend that you use SET @local_variable instead of SELECT @ local_variable .

@ local_variable

A declared variable for which a value is to be assigned.

{ = | += | -= | *= | /= | %= | &= | ^= | |= } Assign the value on the right to the variable on the left.

Compound assignment operator:

Any valid expression . This includes a scalar subquery.

SELECT @ local_variable is typically used to return a single value into the variable. However, when expression is the name of a column, it can return multiple values. If the SELECT statement returns more than one value, the variable is assigned the last value that is returned.

If the SELECT statement returns no rows, the variable retains its present value. If expression is a scalar subquery that returns no value, the variable is set to NULL.

One SELECT statement can initialize multiple local variables.

A SELECT statement that contains a variable assignment cannot be used to also perform typical result set retrieval operations.

A. Use SELECT @local_variable to return a single value

In the following example, the variable @var1 is assigned "Generic Name" as its value. The query against the Store table returns no rows because the value specified for CustomerID doesn't exist in the table. The variable retains the "Generic Name" value.

This example uses the AdventureWorksLT sample database, for more information, see AdventureWorks sample databases . The AdventureWorksLT database is used as the sample database for Azure SQL Database.

Here's the result set.

B. Use SELECT @local_variable to return null

In the following example, a subquery is used to assign a value to @var1 . Because the value requested for CustomerID doesn't exist, the subquery returns no value, and the variable is set to NULL .

C. Antipattern use of recursive variable assignment

Avoid the following pattern for recursive use of variables and expressions:

In this case, it isn't guaranteed that @Var would be updated on a row by row basis. For example, @Var may be set to initial value of @Var for all rows. This is because the order and frequency in which the assignments are processed is nondeterminant. This applies to expressions containing variables string concatenation, as demonstrated below, but also to expressions with non-string variables or += style operators. Use aggregation functions instead for a set-based operation instead of a row-by-row operation.

For string concatenation, instead consider the STRING_AGG function, introduced in SQL Server 2017 (14.x), for scenarios where ordered string concatenation is desired. For more information, see STRING_AGG (Transact-SQL) .

The Transact-SQL code samples in this article use the AdventureWorks2022 or AdventureWorksDW2022 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.

An example to avoid, where using ORDER BY in attempt to order concatenation causes list to be incomplete:

Result set:

Instead, consider:

• DECLARE @local_variable (Transact-SQL)
• Expressions (Transact-SQL)
• Compound Operators (Transact-SQL)
• SELECT (Transact-SQL)
• AdventureWorks sample databases

Was this page helpful?

Additional resources

Using SQL Variables in SQL Server Code and Queries

By: Kenneth A. Omorodion   |   Updated: 2023-07-20   |   Comments   |   Related: > TSQL

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.

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:

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.

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.

Or you could do this:

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.

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.

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

Use the SET statement.

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.

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

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

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

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.

The output of the above query is shown below.

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

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.

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.

The Basics of SQL Server Variables

SQL Variables in Scripts, Functions, Stored Procedures, SQLCMD and More

When to use SET vs SELECT when assigning values to variables in SQL Server

• SQL Variables for Queries and Stored Procedures in SQL Server, Oracle and PostgreSQL
• Execute Dynamic SQL commands in SQL Server
• DECLARE @local_variable (Transact-SQL) - SQL Server | Microsoft Learn
• Variables (Transact-SQL) - SQL Server | Microsoft Learn
• SET @local_variable (Transact-SQL) - SQL Server | Microsoft Learn
• SELECT @local_variable (Transact-SQL) - SQL Server | Microsoft Learn

About the author

Comments For This Article

Related Content

How to use @@ROWCOUNT in SQL Server

SQL Declare Variable to Define and Use Variables in SQL Server code

Auto Generate SQL Variable Syntax for Table Column Names

Nullability settings with select into and variables

Free Learning Guides

Learn Power BI

What is SQL Server?

Download Links

Become a DBA

What is SSIS?

Related Categories

Change Data Capture

Common Table Expressions

Dynamic SQL

Error Handling

Stored Procedures

Transactions

Development

Date Functions

System Functions

JOIN Tables

SQL Server Management Studio

Database Administration

Performance

Performance Tuning

Locking and Blocking

Data Analytics \ ETL

Microsoft Fabric

Azure Data Factory

Integration Services

Popular Articles

Date and Time Conversions Using SQL Server

Format SQL Server Dates with FORMAT Function

SQL Server CROSS APPLY and OUTER APPLY

SQL Server Cursor Example

SQL CASE Statement in Where Clause to Filter Based on a Condition or Expression

SQL NOT IN Operator

How to install SQL Server 2022 step by step

SQL EXISTS Use Cases and Examples

DROP TABLE IF EXISTS Examples for SQL Server

Rolling up multiple rows into a single row and column for SQL Server data

Format numbers in SQL Server

SQL Convert Date to YYYYMMDD

Script to retrieve SQL Server database backup history and no backups

Resolving could not open a connection to SQL Server errors

SQL Server Management Studio Dark Mode

Setup Excel as Front End Application for SQL Server

SQL Server PIVOT and UNPIVOT Examples

How to monitor backup and restore progress in SQL Server

An Introduction to SQL Triggers

Using MERGE in SQL Server to insert, update and delete at the same time