根据来自 SP 的结果集动态创建临时表 [英] Dynamically create temp table based on resultset from SP

问题描述

我有一个调用另一个 SP 的 SP，结果集需要过滤到我感兴趣的列.

I have a SP that calls another SP and the resultset needs to be filtered to only the columns that I am interested in.

DECLARE @myTable TABLE
(
  Field1 INT,
  Field2 INT,
  Field3 INT
)

--If someSP returns say 30 params and I need only 3 params, I don't want to declare all 30 in my temp table @myTable

INSERT INTO @myTable
(Field1, Field2, Field3)   
EXEC someSP          --Need to selectively filter recordset from SP
  @InputParam1 = 'test'

如果我不能这样做，我想根据 someSP 的结果集动态创建临时表(这样可以在修改 someSP 以添加新参数时减轻维护问题，我也不需要修改此过程

If I cannot do this, I would want to create the temp table DYNAMICALLY based on the resultset from someSP (This way it relieves maintenance issues when someSP is modified to add a new param, I dont need to modify this proc as well

推荐答案

简短回答:不，你不能那样做.

Short answer: no, you can't do that.

您必须使用将从存储过程返回的确切列数预先声明您的临时表.

You have to pre-declare your temp table with the exact number of columns that will be returned from the stored proc.

解决方法是使用持久表.例如，您的数据库中可以有一个名为 someSPResults 的永久表.每当 someSP 更改为具有不同数量的输出列时，请在部署过程中更改 someSPResults 的格式.

The workaround is to use persistent tables. For example, you could have a permanent table in your database called someSPResults. Whenever someSP is changed to have a different number of output columns, change the format of someSPResults as part of the deployment.

然后你可以这样做:

insert into dbo.someSPresults
exec someSP

或者在 someSP 内部，您可以将结果直接插入到 someSPresults 表中，作为执行的正常部分.您只需要确保准确识别 someSPresults 表中的哪些记录来自 someSP 的每次执行，因为存储的 proc 可能同时被触发多次，从而将大量数据转储到 someSPresults 中.

Or inside someSP, you can have the results be inserted directly into the someSPresults table as a normal part of execution. You just have to make sure to identify exactly which records in the someSPresults table came from each execution of someSP, because that stored proc could be fired multiple times simultaneously, thereby dumping a lot of data into someSPresults.

这篇关于根据来自 SP 的结果集动态创建临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！