Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Column Definitions for Stored Procedure Result Set

RSS
Modified on Fri, Nov 06, 2015, 11:53 AM by Administrator Categorized as SQL Server

Overview

Starting in SQL Server 2012, Microsoft added the sp_describe_first_result_set system stored procedure to return the metadata of the first result set from a stored procedure. This article describes how to retrieve that information from SQL Server 2008.

Reference


Procedure

1. Create loopback linked server.

IMPORTANT NOTE: If @@SERVERNAME doesn't properly return the name of your instance, then replace it in the following script with the correct string literal.

EXEC master..sp_addlinkedserver 
    @server = 'loopback',  
    @srvproduct = '',
    @provider = 'SQLNCLI',
    @datasrc = @@SERVERNAME;

EXEC master..sp_serveroption 
    @server = 'loopback', 
    @optname = 'DATA ACCESS',
    @optvalue = 'TRUE';

2. For each stored procedure of interest, execute the following script, replacing the stored procedure call with a call to the stored procedure of interest.

if object_id('tempdb..#tmp') is not null drop table #tmp
go
/*  TODO: The following line is only an example.  Replace 'exec [MyDatabase].[MySchema].[sp_MyStoredProc] 0,0,null'
    with your own stored procedure call, including parameter values
*/
select top 10 * into #tmp from openquery(loopback, 'exec [MyDatabase].[MySchema].[sp_MyStoredProc] 0,0,null')

SELECT 
    c.name
    ,[type] = t.name
    ,c.max_length
    ,c.[precision]
    ,c.scale
    ,c.is_nullable

FROM 
    tempdb.sys.columns AS c

    inner JOIN tempdb.sys.types AS t
        ON c.system_type_id = t.system_type_id
        AND c.user_type_id = t.user_type_id

WHERE 
    c.[object_id] = object_id('tempdb..#tmp')

Reusable Code

create procedure dbo.GetStoredProcedureOutputStructure
    @spName nvarchar(300)
as
/*--- Begin Testing ---*/
/*
declare 
     @spName    nvarchar(300)   = '[MySchema].[sp_MyStoredProc]'
*/
/*--- End Testing ---*/

if object_id(@spName) is not null begin

    /*--- Calculate Param List as All Nulls (separated by commas) ---*/
    declare @xml xml = (
        select 
            [@v] = 'null'
        from 
            sys.procedures pr 
            inner join sys.parameters p 
                on pr.object_id = p.object_id
            inner join sys.schemas s
                on pr.schema_id = s.schema_id
        where 1=1
            and '[' + s.name + '].[' + pr.name +']' = @spName
        for xml path ('x')
        )

    declare @spParams nvarchar(4000) = convert(nvarchar(4000), @xml)

    set @spParams = replace(@spParams, '"/><x v="', ', ')
    set @spParams = replace(@spParams, '<x v="', '')
    set @spParams = replace(@spParams, '"/>', '')


    /*--- Calculate OpenQuery statement and call it ---*/
    if object_id(N'tempdb..##tmp') is not null drop table ##tmp

    set @spName = '[' + db_name() + '].' + @spName

    declare @openquery nvarchar(4000) = 'select top 10 * into ##tmp from openquery(loopback, ''exec ' + @spName + ' ' + @spParams + ''')'
     
    exec (@openquery)

    /*--- Return Output Structure as XML ---*/
    SELECT 
         [@Name] = c.name
        ,[@DataType] = t.name
        ,[@MaxLength] = c.max_length
        ,[@Precision] = c.[precision]
        ,[@Scale] = c.scale
        ,[@IsNullable] = c.is_nullable

    FROM 
        tempdb.sys.columns AS c

        inner JOIN tempdb.sys.types AS t
            ON c.system_type_id = t.system_type_id
            AND c.user_type_id = t.user_type_id

    WHERE 
        c.[object_id] = object_id('tempdb..##tmp')

    for xml path ('ColumnDef')

end

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.