Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

Database User and Programming Tips

Posted May 4, 2017

Setting Variables in Calling T-SQL Code While Using sp_executesql

By Greg Larsen

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

Database User and Programming Tips Archives