CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Johannes Wentu
Johannes Wentu

Reputation: 989

How to generate a create table for the result of a stored procedure

I have several Stored Prodedures with many many columns. For each of them I need to put the result of the SProc in a temp table, therefore I have to first declare these tables. This is a tedious work that I'd like to automate. Is it possible, given the name of a SProc, to obtain the script of a table that can contain the result of the SProc? I Would try with "sp_describe_first_result_set" BUT several of my SProcs have their only result set generated via DynamicSQL (that's the reason for the many columns...) and it seems "sp_describe_first_result_set" is not working in that case (unsurprisingly)

Thank you

Upvotes: 0

Views: 1440

Answers (1)

gotqn
gotqn

Reputation: 43636

You cannot fight the limitations of sp_decribe_first_result_set in any way. I have spent a lot of time (and continue spending some each couple of mounts) and the result is we need to learn to live with them.

As to your question, I have also stored procedure which is generating different T-SQL dynamic statements depending on its parameters. And like you, I need to store its result set sometimes (and we need to define the structure of the temporary table before storing the data).

In order to improve this, I've added a mode, which is describing the result set, instead of returning the data. Its something like this:

IF [dbo].[fn_Utils_RegexIsMatch] ('(?i)\[DescribeFirstResultSet]', @MiscSettings) = 1
    BEGIN;
        SET @DynamicTSQLStatement = [dbo].[fn_Utils_RegexReplace] (@DynamicTSQLStatement, 'CREATE TABLE #([^\s]+)', 'DECLARE @$1 TABLE');
        SET @DynamicTSQLStatement = REPLACE(@DynamicTSQLStatement, '#', '@');
        SET @DynamicTSQLStatement = [dbo].[fn_Utils_RegexReplace] (@DynamicTSQLStatement, 'DROP TABLE IF EXISTS.+?;', '');

        SELECT [column_ordinal]
              ,REPLACE([name], '@', '#') AS [name]
              ,[system_type_name]
              ,IIF([column_ordinal] = 1, '', ',') + '[' + REPLACE([name], '@', '#') + '] ' + UPPER([system_type_name]) AS [column_definition]
        FROM [sys].[dm_exec_describe_first_result_set] (@DynamicTSQLStatement, NULL, 0); 

        EXEC [sys].[sp_describe_first_result_set] @DynamicTSQLStatement;

        RETURN;
    END;
ELSE
    BEGIN;
        EXEC sp_executesql @DynamicTSQLStatement;
    END;

So, in my case, the developer is setting the columns ze needs in a parameter but is calling the stored procedure with the description option. So, the result is like this:

enter image description here

Few notes:

  • you can try to use the definition in order to create a temporary table and then in dynamic T-SQL statement to execute your stored procedure
  • note, that I am replacing/converting my temporary tables to table variables (this is needed due limitations again)
  • if you want to use CLR regex replace function check this answer

Upvotes: 1

Related Questions