[New England] Kalen Delaney Internals and Query Tuning - in the Boston area October 12-16

Adam Machanic — Thu, 23 Jul 2009 14:16:00 GMT

In case you missed Kalen's post , we're excited to announce that she will be joining us here on the East Coast in October to deliver her SQL Server 2005/2008 Internals and Query Tuning seminar. This is, to my knowledge, the most advanced public SQL Server...(read more)

Replacing xp_execresultset in SQL Server 2005

Adam Machanic — Thu, 19 Oct 2006 14:13:00 GMT

SQL Server 2000 included a very useful extended stored procedure called xp_execresultset. This XP had two parameters: @cmd and @dbname. @cmd was expected to be a SELECT statement that would produce a single column of output, each row of which would produce a valid query. @dbname was used to specify the database that both the initial query and the resultant queries would be executed in.

For example:

EXEC xp_execresultset 
    @cmd=N'SELECT ''SELECT 1''', 
    @dbname=N'tempdb'

In this case, tempdb would be used to execute the statement SELECT ''SELECT 1''. This statement produces a single row with the value SELECT 1, which is then executed in tempdb. The final output is a single result set containing the value 1.

Multi-row statements are also allowed:

EXEC xp_execresultset 
    @cmd=N'SELECT ''SELECT 1'' UNION ALL SELECT ''SELECT 2''', 
    @dbname=N'tempdb'

In this case, the initial SELECT produces two rows, each with its own statement. Then each row is processed as an individual query, thereby producing two single-row result sets, one with the value 1, and one with the value 2.

We can verify that the first statement is run in the specified database by using the DB_ID() function in the outer query:

USE master

EXEC xp_execresultset 
    @cmd=N'SELECT ''SELECT ''+CONVERT(NVARCHAR, DB_ID())', 
    @dbname=N'tempdb'

In this case the end result is 2, indicating that the outer query was run in tempdb rather than master.

So now that I've covered xp_execresultset's usage in SQL Server 2000, the bad news: this XP, although quite useful in many cases, has been removed in SQL Server 2005. This was pointed out to me by a post today in the MSDN forums by Marko B. Simic (thanks, Marko!)

The solution, luckily, is pretty simple: We can re-create this XP as a stored procedure in SQL Server 2005, using a few tricks to make the job easier. Following is the replacement stored procedure I've come up with:

CREATE PROC ExecResultSet
    @cmd NVARCHAR(MAX),
    @dbname NVARCHAR(255)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @x TABLE
    (
        sql NVARCHAR(MAX),
        num INT IDENTITY(1,1)
    )

    DECLARE @input_sql NVARCHAR(355)
    SET @input_sql = N'EXEC ' + @dbname + '..sp_executesql @stmt=@cmd'

    INSERT @x (sql)
    EXEC sp_executesql 
        @input_sql, 
        N'@cmd NVARCHAR(MAX)', 
        @cmd

    DECLARE @sql NVARCHAR(MAX)
    SELECT @sql =
    (
        SELECT sql + ';' AS [data()]
        FROM @x
        ORDER BY num
        FOR XML PATH('')
    )

    EXEC sp_executesql 
        @input_sql, 
        N'@cmd NVARCHAR(MAX)', 
        @sql
END
GO

Note that I've tried to faithfully reproduce the original functionality of xp_execresultset. This means that the resultant rows are concatenated and executed as a single batch. This can be good in some cases -- for instance, your first row can contain some variable declarations that will be used by later rows. However, this can also cause problems if you need to declare a variable on a per-row basis.

This stored procedure is fairly simple: It executes the input @cmd, concatenates the resultant rows, and finally executes everything as a single batch. Its syntax is identical to the original XP. Modifying this stored procedure to make it a bit more flexible and execute each row as its own batch is a simple matter of using a cursor to take each row individually, rather than employing the FOR XML PATH concatenation trick. However, I'm going to leave it as-is for now so that it can be used directly in places where you would have used xp_executesql previously.

Adam Machanic : SQL Server 2005

[New England] Kalen Delaney Internals and Query Tuning - in the Boston area October 12-16

Replacing xp_execresultset in SQL Server 2005