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

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

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.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles