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

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


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

Featured Database Articles

MS SQL

Posted Dec 17, 2003

T-SQL Programming Part 4 - Setting Variables in Calling T-SQL Code While Using sp_executesql

By Gregory A. Larsen

Occasionally you need to build dynamic T-SQL that not only requires the T-SQL code to be dynamic, but also requires the dynamic T-SQL to return values from the dynamic code to the calling T-SQL code. There are a number of different ways to write and execute dynamic code. For the purpose of this article, I will be using the sp_executesql stored procedure (SP) for executing my dynamic code. In this article, I will show you how to use the sp_executesql SP to allow a T-SQL coder to set variables in the calling T-SQL code when executing dynamic T-SQL.

The sp_executesql SP is a system SP provided by Microsoft. This SP can be found in the master database. Here is the calling syntax for this SP:

sp_executesql [@stmt =] stmt
[ 
    {, [@params =] N'@parameter_name  data_type [,...n]' } 
    {, [@param1 =] 'value1' [,...n] }
]

Where [@stmt =] stmt is the dynamic T-SQL statement that you would like to execute, [@params =] N'@parameter_name  data_type [,...n]' is used to identify the list of variables and the data type for each dynamic variable that is contained in the dynamic T-SQL statement, and [@param1 =] 'value1' [,...n] is used to provide values for each variable used in the dynamic T-SQL statement being executed. More information about this stored procedure, and how to use it can be found in Microsoft SQL Server Books Online.

In order to show you how to return variables from the sp_executesql SP to your calling T-SQL, I will show you a real simple example. My example will return the numbers of records in a table that meet a specific WHERE criteria that is pass dynamically to the sp_executesql SP. Here is the code:

use Northwind
go
declare @RECCNT int
declare @ORDID varchar(10)
declare @CMD Nvarchar(100)
set @ORDID = 10436
SET @CMD = 'SELECT @RECORDCNT=count(*) from [Orders]' + 
           ' where OrderId < @ORDERID'
print @CMD
exec sp_executesql @CMD,
                   N'@RECORDCNT int out, @ORDERID int', 
                   @RECCNT out,
                   @ORDID  
print 'The number of records that have an OrderId' +  
      ' greater than ' + @ORDID + ' is ' + 
      cast(@RECCNT as char(5))

In this example you can see that the table from which I am returning the record count is the "Orders" table in the "Northwind" database. To make this code dynamic, the WHERE statement criteria is built on the fly by using a value passed in to the sp_executesql SP call which will set the @ORDERID variable in the dynamic T-SQL string. The record count for the number of Orders records that have an OrderId less than the @ORDERID variable value will be returned in a variable named @RECORDCNT. Both these local variables are specified in the dynamic T-SQL command, which is passed to sp_executesql SP in the string @CMD. These variables are not declared the calling T-SQL code, but instead are declared within the sp_executesql SP.

Note that the above code has a couple of PRINT statements. The first PRINT statement in the code above displays the dynamic T-SQL command to be executed by the sp_executesql SP. Here is the dynamic T-SQL code that is displayed when this first PRINT statement is executed:

SELECT @RECORDCNT=count(*) from [Orders] where OrderId < @ORDERID

Since this T-SQL statement contains variables, the sp_executesql (SP) requires that the calling T-SQL code identify these variables, and their format in the parameters passed to this SP. Specifying the definition of these variables is done in the second parameter of the sp_executesql call above (N'@RECORDCNT int out, @ORDERID int'). As you can see the @RECORDCNT variable is defined as output. This allows this variable to return a value from the sp_executesql to the calling T-SQL code. Defining the definitions of these variables allows the sp_executesql SP to define these variables locally within the SP code.

When variables are used in the calling program to pass and return values you also need to identify these variables to sp_executesql SP. You need to do this so that the sp_executesql SP knows which variables in the calling program are associated with the variables placed in the command string, which is passed in the first parameter (@stmt) of the sp_executesql SP. In this example, I wanted to associate the variable @RECORDCNT with the variable @RECCNT, which is defined in my calling T-SQL code, as well as associate @ORDERID with the @ORDID variable, which is also defined in my calling T-SQL code. These variable associations are done using the final parameters passed to the sp_executesql SP; in my example this would be the third and forth parameters in the sp_executesql statement in the code above. The order of the third and forth parameters, in this case, need to match the order in which these variables are defined in the second parameter of the sp_executesql call. Also important is to identify that the @RECCNT variable is output. This allows the sp_executesql statement to set the @RECCNT variable with the results of @RECORDCNT when the sp_executesql statement is processed.

The output of the above script, when run on my server, displays the following output from the final PRINT statement in the script:

The number of records that have an OrderId greater than 10436 is 188

Conclusion

Although I have shown you only a simple example, which could have easily been done a number of other ways, hopefully you see the value of how the sp_executesql SP can process dynamic T-SQL and return results to variables in the calling T-SQL code. If you already have a need for this functionality then you should be able to expand on this method to meet your needs. As you can see, it is relatively straightforward to get your dynamic T-SQL code to set variables in your calling T-SQL when using the sp_executesql SP.

» See All Articles by Columnist Gregory A. Larsen



MS SQL Archives

Comment and Contribute

 


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

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date