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.
The T-SQL syntax for declaring a variable in SQL Server is as follows:
DECLARE @variable_name data_type [= value];
Let's look at the query below to understand how the variable works in SQL Server.
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:
DECLARE @employee_name VARCHAR(50); SET @employee_name = 'Rajendra';
DECLARE @current_date DATE = GETDATE();
DECLARE @tax_rate DECIMAL(4,2); SET @tax_rate = 0.04;
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.
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
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.
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
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.
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;
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
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
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.
Article Last Updated: 2023-08-16
