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 
[AdventureWorks2016CTP3].[Sales].[SalesOrderHeader]
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