THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Replacing xp_execresultset in SQL Server 2005

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.
Published Thursday, October 19, 2006 11:13 AM by Adam Machanic
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Phil Factor said:

Adam,

This is very clever code. Just the sort of thing one needs in a hurry when SQL 2000 stuff stops working after upgrading to 2005!

Phil

January 18, 2007 10:34 AM
 

Mark McGinty said:

Indeed, everything that Phil said, on the spot, and spot on.

-MM

September 4, 2007 12:12 AM
 

Vedran said:

Cheers m8!

you saved me :-)

November 15, 2007 4:27 AM
 

Matt Gingery said:

This code works for simple queries but fails on my system when it encounters any of the following in the data referenced in @cmd:

- a single quote

- a double quote

- a carriage return

- a greater than sign

- a less than sign

- or an ampersand.

These are special characters in the XML language so it encodes them with special characters that cause an error when the SQL is executed.

For example:

create table ##tmp ( txt varchar( 4000 ))

insert ##tmp

select top 3 'SELECT CASE WHEN ''VIEW'' <> ''' + TABLE_TYPE + ''' THEN  ''' + TABLE_NAME + '''

ELSE ''VIEW'' END'

FROM MSDB.INFORMATION_SCHEMA.TABLES

ORDER BY 1

SELECT TXT + ';

' AS [data()]

FROM ##tmp

FOR XML path('')

Returns this data:

SELECT CASE WHEN &apos;VIEW&apos; &lt;&gt; &apos;BASE TABLE&apos; THEN  &apos;backupfile&apos; &#x0D;

ELSE &apos;VIEW&apos; END;&#x0D;

SELECT CASE WHEN &apos;VIEW&apos; &lt;&gt; &apos;BASE TABLE&apos; THEN  &apos;backupfilegroup&apos; &#x0D;

ELSE &apos;VIEW&apos; END;&#x0D;

SELECT CASE WHEN &apos;VIEW&apos; &lt;&gt; &apos;BASE TABLE&apos; THEN  &apos;backupmediafamily&apos; &#x0D;

ELSE &apos;VIEW&apos; END;&#x0D;

In my version of the procedure, I added this line of code to get it to work:

SET @sql =

REPLACE (

REPLACE (

REPLACE (

REPLACE (

REPLACE (

REPLACE ( @sql , '&#x0D;' , CHAR(13) )

, '&quot;' , '"' )

, '&gt;'   , '>' )

, '&lt;'   , '<' )

, '&amp;'  , '&' )

, '&apos;' , '''' )

Please comment if there some better way of handling this or if I missed any other special characters that might cause problems in the formatted XML.

Thanks,

Matt

June 18, 2008 7:51 PM
 

Adam Machanic said:

Hi Matt,

Thanks for the comment.  You're absolutely correct that this is a major problem.  What you're seeing is called "entitization", and unfortunately I learned how to deal with it only after writing this post.  To fix the issue, change the final SELECT in the query to the following:

SELECT

@sql = mydata.value('/row[1]/x[1]', 'varchar(max)')

FROM

(

SELECT x

FROM

(        

SELECT

sql + ';' AS [data()]

FROM @x

ORDER BY num

FOR XML PATH(''), TYPE

) y (x)

FOR XML RAW, TYPE

) d (mydata)

July 9, 2008 11:52 AM
 

Gops said:

Thank You!

This was very useful.

October 20, 2009 4:25 PM
 

Eugen Mihailescu said:

I have found one situation that this will not work 100%, even if the fix is quick & easy.

Example:

   use myDB;

   create ##SQL (ID int identity(1,1), Stmt varchar(8000));

   insert into ##SQL select 'select fld1,fld2,fld3 from myTable1';

   insert into ##SQL select 'UNION ALL';

   insert into ##SQL select 'select A,B,C from myTable2';

   EXEC ExecResultSet N'select * from ##SQL order by ID', N'myDB';

This will produce an error because of the use of ';' in the last SQL statement if ExecResultSet:

   sql + ';' AS [data()]

This fix is quick & easy, just get rid of ';' because in this situation is not necessary at all or at least replace it with a single space, that will cover other situations too.

What do you think?

February 8, 2011 6:54 AM
 

Another Solution said:

Hi I've just Found another Solution for this problem in just 3 steps :-

1- you define a linked server to the target Server with the default catalog  = Target database Name

2- Put your Code in a @Variable

2-use 'execute at'

eg. execute (@variable) at linked_server_name

January 16, 2013 7:04 AM
 

Renato S said:

Adam,

Thanks for the proc!

Really helpful.

[]'s

October 24, 2014 7:39 AM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement