Xp_execresultset
T-SQL programmers commonly want to execute dynamic code longer than 4000 characters and to execute code constructed by a query. But sp_executesql limits code to 4000 bytes—the nvarchar data type's limitation. And although running queries against system tables and INFORMATION_SCHEMA views is a convenient way to construct administrative code, you have to run the query and copy the code from the Query Analyzer Results pane to the Execution pane before you execute it.
The undocumented xp_execresultset extended stored procedure provides a way for programmers to satisfy both desires. As an example of executing dynamic code longer than 4000 characters, let's store some long, dynamically constructed code in separate rows of a table. Running the code in Listing 5, page 21, creates the TSQLCommands table and populates it with three rows that together construct a valid statement. Note a couple of important things here. First, you have to design the table where you store the command elements so that you'll be able to sort them correctly. I used the cmdrow column to store a sequence of numbers that determine the position of the command's element within the complete command. Second, you can't break a word in the middle and continue it in the next row. Before executing xp_execresultset, run the following query:
SELECT cmdtext FROM TSQLCommands
WHERE cmdid = 1 ORDER BY cmdrow
The result is the valid T-SQL command that Figure 2 shows.
The xp_execresultset procedure accepts two arguments: an nvarchar string containing the query whose results you're executing and an nvarchar string containing the name of the database you're running the code against. To execute the results of the preceding query in Pubs, you would run the following code:
EXEC master..xp_execresultset
N'SELECT cmdtext FROM
tempdb..TSQLCommands WHERE
cmdid = 1 ORDER BY cmdrow',
N'pubs'
You get the au_lname and au_fname columns from the authors table, as Figure 3 shows.
As an example of executing query-constructed code, suppose you want to write code that counts the number of rows in all the base tables in your database. First, run the following code to create the #NumRows temporary table:
CREATE TABLE #NumRows(table_name
SYSNAME, owner SYSNAME,
numrows int)
Then, run the code at callout A in Listing 6 to execute the query that generates the INSERT statements that populate the table. You get the results that Figure 4 shows.
Usually, you'd copy the generated INSERT statements to Query Analyzer's Execution pane and execute them. However, you can use xp_execresultset to run the query, then execute its results as callout B in Listing 6 shows.
Note that because xp_execresultset accepts the query string as a character-based argument, you need to prefix and suffix the string with single quotes and duplicate each quote within the string. To examine the results, query the #NumRows temporary table:
SELECT * FROM #NumRows
In this article, I started exploring T-SQL back doors—undocumented features that T-SQL programmers use—mainly those related to stored procedures. In my next column, I'll discuss back doors related to user-defined functions (UDFs). As you've probably noticed, the back doors that I explored have a lot of useful functionality, but you need to be aware of the risks I mentioned. I hope to see those back doors become supported front doors in a future release of SQL Server.
End of Article
Prev. page
1
2
3
4
[5]
next page -->
