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

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

Problem

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.

Solution

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 @variable_name data_type [= value];

• 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.

DECLARE @ProductID int = 778

SELECT [SalesOrderID]
      ,[CarrierTrackingNumber]
      ,[OrderQty]
      ,[ProductID]
      ,[SpecialOfferID]
      ,[UnitPrice]
      ,[LineTotal]
FROM [AdventureWorks2019].[Sales].[SalesOrderDetail]
WHERE ProductID = @ProductID

SELECT * FROM employees WHERE salary > @num_of_employees;

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.

DECLARE @ProductID int

SET @ProductID = 778

SELECT [SalesOrderID]
       ,[CarrierTrackingNumber]
      ,[OrderQty]
      ,[ProductID]
      ,[SpecialOfferID]
      ,[UnitPrice]
      ,[LineTotal]
FROM [AdventureWorks2019].[Sales].[SalesOrderDetail]
WHERE ProductID=@ProductID

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

Character Variable

DECLARE @employee_name VARCHAR(50);
SET @employee_name = 'Rajendra';

Date Variable

DECLARE @current_date DATE = GETDATE();

Decimal Variable

DECLARE @tax_rate DECIMAL(4,2);
SET @tax_rate = 0.04;

Variable Declaration for Multiple SQL Server Variables

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

DECLARE @Name varchar(50)
DECLARE @Age tinyint
DECLARE @DOJ int

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

DECLARE @Name varchar(50), @Age tinyint, @DOJ int

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.

DECLARE @AvgUnitPrice int, @AvgOrderQty int, @AvgLineTotal int

SELECT
    @AvgUnitPrice = Avg(UnitPrice)
   ,@AvgOrderQty = Avg(OrderQty)
   ,@AvgLineTotal = Avg(LineTotal)
FROM [AdventureWorks2019].[Sales].[SalesOrderDetail]

SELECT 
    @AvgUnitPrice as AvgLineTotal
   ,@AvgOrderQty as AvgOrderQty
   ,@AvgLineTotal as AvgLineTotal

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.

CREATE PROCEDURE dbo.GetPersonInfo
    @NationalIDNumber INT
AS
BEGIN
    DECLARE @JobTitle VARCHAR(50)
    DECLARE @BirthDate date
 
    SELECT @JobTitle = JobTitle, @BirthDate = BirthDate
    FROM [AdventureWorks2019].[HumanResources].[Employee]
    WHERE [NationalIDNumber] = @NationalIDNumber
 
    -- Use the variables for further processing
    PRINT 'Job Title: ' + @JobTitle
    PRINT 'DOB: ' + CAST(@BirthDate AS VARCHAR(10))
END
GO

EXEC GetPersonInfo 295847284

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

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.

DECLARE @SalesOrderID int=71951
DECLARE @SalesOrderNumber varchar(20)
DECLARE @TotalDue DECIMAL(10, 2)
DECLARE @SalesPersonID varchar(20)

DECLARE SalesOrder_Cursor CURSOR FOR
SELECT SalesOrderNumber , TotalDue, SalesPersonID
FROM [AdventureWorks2019].[Sales].[SalesOrderHeader]
WHERE SalesOrderID>=@SalesOrderID
  AND SalesPersonID is not null

OPEN SalesOrder_Cursor

FETCH NEXT FROM SalesOrder_Cursor INTO @SalesOrderNumber, @TotalDue, @SalesPersonID

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Perform operations using the variables
    PRINT 'Sales Person ID: ' + @SalesPersonID
    PRINT 'Sales Order: ' + CAST(@SalesOrderNumber AS VARCHAR(10))
    PRINT 'Total Due Amount: ' + CAST(@TotalDue AS VARCHAR(10))

    -- Get next set of data from cursor
    FETCH NEXT FROM SalesOrder_Cursor INTO @SalesOrderNumber, @TotalDue, @SalesPersonID
END

CLOSE SalesOrder_Cursor
DEALLOCATE SalesOrder_Cursor

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.

DECLARE @table_name VARCHAR(50) = 'SalesOrderHeader';
DECLARE @schema varchar(20) = 'Sales'
DECLARE @SalesOrderID varchar(20) = 71951
DECLARE @sql_stmt NVARCHAR(MAX);

SET @sql_stmt = 'SELECT * FROM ' + @schema+ '.'+ QUOTENAME(@table_name) + ' WHERE SalesOrderID ='+ @SalesOrderID;

EXEC sp_executesql @sql_stmt;

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.

DECLARE @SalesOrderIDStart INT = 43659, @SalesOrderIDEnd INT = 43759

WHILE @SalesOrderIDStart <= @SalesOrderIDEnd
BEGIN
    SELECT  
       [AccountNumber], [CustomerID], [SalesPersonID]
      ,[TerritoryID], [BillToAddressID], [ShipToAddressID]
      ,[ShipMethodID], [CreditCardID], [CreditCardApprovalCode]
      ,[TaxAmt], [Freight], [TotalDue]
    FROM [AdventureWorks2019].[Sales].[SalesOrderHeader]
    WHERE SalesOrderID = @SalesOrderIDStart
    
    SET @SalesOrderIDStart = @SalesOrderIDStart + 1;
END

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.

CREATE TABLE Employee
(
   ID int PRIMARY KEY,
   First_name varchar(50),
   Country varchar(20)
)
GO

BEGIN TRY
    INSERT INTO Employee Values(1, 'Rajendra','India')

    --Inserting Duplicate Record which will error out due to PRIMARY KEY
    INSERT INTO Employee Values(1, 'Rajendra','India')
END TRY

BEGIN CATCH
    -- Declare variables to store error information
    DECLARE @ErrorMessage NVARCHAR(MAX);
    SELECT @ErrorMessage = ERROR_MESSAGE()
    PRINT 'An error occurred: ' + @ErrorMessage; 
END CATCH

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

Conclusion

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.

Next Steps

• Explore SQL Server declare variable documentation on Microsoft docs.
• Read exciting tips about functions on MSSQLTips.

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

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

View all my tips