Home Uncategorized Replacing xp_execresultset in SQL Server 2005

    Replacing xp_execresultset in SQL Server 2005

    500
    9

    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.

    Previous articleExploring the secrets of intermediate materialization
    Next articleMedians, ROW_NUMBERs, and performance
    Adam Machanic helps companies get the most out of their SQL Server databases. He creates solid architectural foundations for high performance databases and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has contributed to numerous books on SQL Server development. A long-time Microsoft MVP for SQL Server, he speaks and trains at IT conferences across North America and Europe.

    9 COMMENTS

    1. 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

    2. 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;
      ELSE &apos;VIEW&apos; END;
      SELECT CASE WHEN &apos;VIEW&apos; &lt;&gt; &apos;BASE TABLE&apos; THEN  &apos;backupfilegroup&apos;
      ELSE &apos;VIEW&apos; END;
      SELECT CASE WHEN &apos;VIEW&apos; &lt;&gt; &apos;BASE TABLE&apos; THEN  &apos;backupmediafamily&apos;
      ELSE &apos;VIEW&apos; END;
      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 , ‘ ’ , 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

    3. 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)

    4. 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?

    5. 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

    Comments are closed.