Setting Variables in Calling T-SQL Code While Using sp_executesql

In this short tip, I will show you how to set variables in your calling T-SQL code when using sp_execute sql.

Suppose you want to set a variable in your calling T-SQL Code with the sum of the TotalSales for all 2011 sales records in the AdventureWorksCTP3.Sales.SalesOrderHeader table. To meet that requirement all you need to do is run the following code:

DECLARE @TotalSalesOutput money;
DECLARE @CMD nvarchar(4000);
SET @CMD = '
SELECT @TotalSales= SUM(SubTotal) FROM 
WHERE YEAR(OrderDate) = 2014;'
EXEC sp_executesql  @stmt=@CMD
                   ,@params = N'@TotalSales money output'
                   ,@TotalSales = @TotalSalesOutput output;
SELECT @TotalSalesOutput As TotalSalesFor2014;

Here I have defined a string variable @CMD.  This variable will contain the dynamic T-SQL code that will be executed by the stored procedure sp_executesql.  The @CMD is passed as the first parameter (@stmt) to sp_executesql.    The second parameter @params defines the what parameters will be used in the dynamic sql code.   In this example I told sp_executesql that the @TotalSales variable was a money field that will contain output.  The last parameter I set is the @TotalSales parameter.  This is the variable I set in my dynamic sql.  For this parameter, I set it to the variable that I want to set in my calling program (@TotalSalesOutput), and I identify it as an output variable.   When I run this code the variable @TotalSalesOutput will be set to the total sales amount for 2014 in my calling T-SQL code.

See all articles by Greg Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles