如何为存储过程的结果生成创建表 [英] How to generate a create table for the result of a stored procedure

问题描述

我有几个包含许多列的存储过程.对于它们中的每一个，我需要将 SProc 的结果放在临时表中，因此我必须首先声明这些表.这是一项乏味的工作，我想自动化.是否有可能，给定一个 SProc 的名称，获得一个可以包含该 SProc 结果的表的脚本?我会尝试使用sp_describe_first_result_set"，但是我的几个 SPRC 的唯一结果集是通过 DynamicSQL 生成的(这就是许多列的原因......)，而且似乎sp_describe_first_result_set"在这种情况下不起作用(不出所料)

谢谢

解决方案

一些注意事项:

• 您可以尝试使用定义来创建临时表，然后在动态 T-SQL 语句中执行您的存储过程
• 请注意，我正在将临时表替换/转换为表变量(由于限制再次需要这样做)
• 如果您想使用 CLR regex replace 功能，请检查此answer

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

解决方案

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:

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

这篇关于如何为存储过程的结果生成创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！