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.