how to create assignment in sql

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

By: Rajendra Gupta | Updated: 2023-08-16 | Comments | Related: > TSQL

SQL Server enables developers to store, retrieve, and manipulate data using the SQL language. However, as scripts and stored procedures become more complex, managing and manipulating data without variables can be challenging. Without variables, developers may need to repeat complex queries or calculations multiple times within a script, resulting in bloated and difficult-to-read code prone to errors.

Furthermore, not using variables can make it challenging to handle errors effectively, as developers may need to repeat the same error-handling code multiple times within a script. It can result in code that is difficult to maintain and makes it harder to identify and fix errors when they occur.

This tutorial will discuss using variables with SQL DECLARE along with various examples.

Variables are prevalent in writing a SQL query. It is helpful to manipulate data within a stored procedure, function, or batch of SQL statements. The variable is beneficial to keep the temporary data for the query batch. You can assign a static value or define dynamic values using SQL query.

Declaring a Single SQL Variable

The T-SQL syntax for declaring a variable in SQL Server is as follows:

Declare statement
@variable_name = variable's name
data_type = variable's data type
Value is an (optional) initial value to assign to the variable value.

Let's look at the query below to understand how the variable works in SQL Server.

It declares a variable named @ProductID of data type integer.
We assigned a static value of 778.

The value assignment when the variable is declared is optional, and you can assign the value during the declaration phase or define it later with the SET keyword as shown in the following example.

You can use the variable with a data type of your choice. A few examples are below:

Character Variable

Date variable, decimal variable, variable declaration for multiple sql server variables.

To declare multiple variables, you can either use different DECLARE keywords such as:

Alternatively, you can use a single DECLARE keyword and separate multiple variables with a comma.

Let's explore the different usages of variables in SQL Server.

Assign Dynamic Values to Variables in SQL Server

Previously, we assigned a static value to the variable declared in the SQL query. You can set the value dynamically as well. For example, the below SQL code declares three variables @AvgUnitPrice, @AvgOrderQty, and @AvgLineTotal. The SELECT statement calculates the unit Price, Order Quantity, and Line Total average. These calculated values are assigned to the variables. Later, you can query these variables to see their values.

Use of Variables in the Stored Procedure

The variables in the stored procedure provide flexibility and control over the data manipulation. The example below shows two variables, @JobTitle and @BirthDate, inside the stored procedure. The stored procedure assigns values to these variables and prints the required information using these variables.

Once we execute the stored procedure with the parameter value @NationalIDNumber, it fetches the @JobTitle and @BirthDate values, as shown below.

Use of Variables in the Stored Procedure

Use of Variable in the Cursors

Variables can be used in the cursors to store and manipulate data. The query below declares a cursor, SalesOrder_Cursor, and fetches the SalesOrderNumber, TotalDue, and SalesPersonID from the AdventureWorks2019 database.

The cursor iterates the SalesOrderIDs, assigns the fetched information to the variables @SalesOrderNumber, @TotalDue, and @SalesPersonID, and prints this information with specific messages.

Use of Variables to Create a Dynamic SQL Statement

Dynamic SQL builds the SQL statement dynamically by combining multiple parts of the queries, such as table names, parameters, and arguments. Variables can be useful in preparing a dynamic SQL statement as they can store the table values, column names, dynamic filters, and parameters.

For example, the following code dynamically creates and executes the SQL statement using the sp_executesql stored procedure.

@schema variable stores the table schema.
@table_name stores the database table name.
@SalesOrderID contains the SalesOrderID.
@sql_stmt contains the dynamic SQL statement using the variables @schema, @table_name, and @SalesOrderID.

Use of Variables to Create a Dynamic SQL Statement

Use of Variables for Controlling the Loop Execution

Variables help control loop executions. You can set conditions, control the loop counter, and determine when the loop should continue or exit.

For example, we want a loop containing information about a few SalesOrderIDs. Here, we defined two variables, @SalesOrderIDStart and @SalesOrderIDEnd, representing the first and last Sales Order ID. The While loop starts from the @SalesOrderIDStart and ends once the SalesOrderID value equals @SalesOrderIDEnd. The SET statement in the while loop increments the SalesOrderID by 1.

Use of Variables for Controlling the Loop Execution

Error Handling Using Variables

Error handling is necessary to catch errors during the code execution. For example, you have defined a primary key on the ID column. The primary key cannot have a duplicate value. Therefore, if anyone tries to insert the duplicate value, it would error out due to the primary key violation.

The following code uses the TRY CATCH block to capture the error due to the primary key violation. The catch block assigns the error message to the variable @ErrorMessage and prints the message.

Learn more about error handling in this tip: Error Handling in SQL Server with TRY CATCH .

Declaring variables enables developers to store and manipulate data during the execution of a script or stored procedure. It is helpful to keep intermediate result sets, control workflow, prepare dynamic SQLs, and error handling.