Output Parameters for Sp_executesql
To execute dynamically constructed T-SQL code, you use the EXEC command and the sp_executesql stored procedure. A question concerning dynamic execution that T-SQL programmers frequently post in the public SQL Server newsgroups (such as msnews.microsoft.com) is, How can I run a calculation within the dynamically executed code and return the result to the invoking batch? For example, suppose you want to write a stored procedure that accepts a table name and a column name and returns the number of distinct values in the specified column. You need to use dynamic execution, but SQL Server Books Online (BOL) says that sp_executesql can have only input parameters, not output parameters. However, the text of system stored procedures such as sp_helpmergearticleconflicts, sp_MSenum_replsqlqueues, and sp_MSenum_replqueues in the master database shows that SQL Server product developers use output parameters with sp_executesql.
T-SQL programmers can also use sp_executesql's undocumented output parameters. Run the code that Listing 4 shows to create the sp_distinctcount stored procedure. This stored procedure accepts table and column names and returns the distinct count of values in the column specified in the @cnt output parameter. The stored procedure's code constructs the T-SQL assignment SELECT statement (a SELECT statement that assigns values to variables) in the @sql variable. The query string assigns the query result to the @distinctcount variable, which is defined in the sp_executesql invocation's second argument as an int OUTPUT parameter. SQL Server passes the pointer for the @cnt parameter to the @distinctcount variable in sp_executesql's third argument.
To test the stored procedure, run it first against Pubs, providing the authors table and the au_lname column as arguments:
DECLARE @n AS int
EXEC pubs..sp_distinctcount
@table_name=authors,
@col_name=au_lname,
@cnt=@n OUTPUT
PRINT 'Distinct count of last
names of authors is: ' +
CAST(@n AS varchar(10))
Note that sp_distinctcount returns the value 22. Next, run it against Northwind, providing the Orders table and the CustomerID column as arguments:
DECLARE @n AS int
EXEC Northwind..sp_distinctcount
@table_name=Orders,
@col_name=CustomerID, @cnt=@n
OUTPUT
PRINT 'Distinct count of
customers that made orders is:
' + CAST(@n AS varchar (10))
This time, the procedure returns the value 89, the number of distinct values in the Orders.CustomerID column, showing that SQL Server used a different table in a different database.
Prev. page
1
2
3
[4]
5
next page 
