如何将存储过程中的多个输出抓取到临时表中

问题描述

我的存储过程返回两个输出.我想在另一个存储过程中使用其中一个.所以试图在临时表中获取第二个输出.但是由于两个输出的结构不同，因此我总是得到列名或提供的值的数量与表定义不匹配."

My stored procedure returns me two outputs. I want to use one of them in another stored procedure. So trying to grab the second output in a temp table. but since the structure of both the outputs are different, there fore I always get "Column name or number of supplied values does not match table definition."

即使我改变了输出的顺序(先输出后第二输出)，它也不起作用.

Even if I change the order of the output(first output second and second output first), it is not working.

我正在建立一个全新的应用程序，我需要在另一个存储过程中再次使用该存储过程.如果我遇到这种情况，可能我需要重写很多代码.

I am establishing a brand new application and I need to use the stored procedure again an d again in another stored procedures. If I face this type of situation, probably I need to rewrite lot of code.

有一个很好的问题，但这仅涵盖一个输出.

There is a very nice questions, but this covers only one output.

将存储过程的结果插入临时表

谢谢

推荐答案

你不能，不能不修改存储过程.

You can't, not without modifying the stored procedure.

在 SQL Server 中，您只能通过 INSERT...EXEC 将存储过程的第一个结果集插入到另一个表中.列数和位置必须完全匹配，并且 INSERT...EXEC 不能嵌套，即不能从 proc1 插入到 proc2 中的表，然后从 proc2 插入到 proc3 中的表中.所以 INSERT...EXEC 是一个完全不令人满意的解决方案.

In SQL Server, you can only insert the first result set of a stored procedure into another table, via INSERT...EXEC. Column count and positions must match exactly, and INSERT...EXEC cannot be nested, ie you cannot insert from proc1 into a table in proc2, and then insert from proc2 into a table in proc3. So INSERT...EXEC is an altogether unsatisfying solution.

解决方法是修改程序以将结果插入到调用范围中定义的临时表中，例如:

The workaround is modify the procedure to insert results into temporary tables defined in the calling scope, eg:

create proc get_some_data as
insert #temp1 (col1, col2) select col1, col2 from table1
insert #temp2 (colA, colB) select colA, colB from table2
go
create table #temp1 (col1 int, col2 int)
create table #temp2 (colA int, colB int)
exec get_some_data
select * from #temp1
select * from #temp2
drop table #temp1
drop table #temp2
go

如果你不能修改程序，你就不走运了. 更正:正如 HABO 亲切指出的那样，你可以使用CLR 来迭代结果集.有关详细信息，请参阅下面的链接.如果您知道自己在做什么并且别无选择，那还不错.

If you can't modify the procedure, you are out of luck. CORRECTION: as HABO kindly pointed out, you could use the CLR to iterate the result sets. See link below for details. Not too bad if you know what you are doing, and have no other choice.

有关在存储过程之间共享数据的更多详细信息，请参阅 Erland Sommarskog 撰写的这篇非常全面的文章:http://www.sommarskog.se/share_data.html

For more details on sharing data between stored procedures, see this very comprehensive article by Erland Sommarskog: http://www.sommarskog.se/share_data.html

这篇关于如何将存储过程中的多个输出抓取到临时表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！