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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Database User and Programming Tips

Posted May 4, 2017

WEBINAR: On-demand webcast

How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >

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 
[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



Database User and Programming Tips Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.