从返回多个结果集的存储过程插入临时表 [英] Insert Into temp table from a stored procedure that returns multiple result sets
问题描述
考虑下面的sql
一个名为 myProc 的存储过程,它返回两个结果集.结果集 1 返回 column1、column2.结果集 2 返回第 3 列、第 4 列、第 5 列.
A stored proc called myProc which returns two result sets. Result set 1 returns column1, column2. Result set 2 returns column 3, column 4, column 5.
由于临时表仅定义了 2 个 int 列,因此以下 sql 将失败.
The following sql will fail since the temp table only has defined 2 int columns.
Create Table #temp1(
Column1 int,
Column2 int)
insert into #temp1 exec myProc
我的问题是是否可以只将第一个结果集插入 #temp1 中?
My question is is it possible to just insert the first result set into #temp1?
推荐答案
旧帖子,但我遇到了同样的问题,虽然上面提到的答案有点相关,但 OP 的问题是关于返回多个集合的 SP.除了重写 SP 以将其拆分为更小的 SP 之外,我能找到的唯一解决方案是编写一个 SQL CLR
过程,该过程执行 SP 并仅返回所需的结果集.该过程获取所需结果集的索引,执行 SqlCommand
以运行初始 T-SQL
SP,然后循环访问 SqlDataReader
结果直到它找到所需的结果集并返回相应的记录.以下代码是 SQL CLR
过程的一部分:
Old post, but I faced the same problem and although the answers mentioned above are a bit related, the OP's question is about SP that returns multiple sets. The only solution I could find, apart from rewriting the SP to split it into smaller SPs, was to write a SQL CLR
procedure that executes the SP and returns only the required result set. The procedure gets the index of the required result set, executes a SqlCommand
to run the intial T-SQL
SP, then loops through a SqlDataReader
results until it finds the desired result set and returns the corresponding records. The following code is part of the SQL CLR
procedure:
SqlDataReader rdr = command.ExecuteReader();
int index = 0;
bool bContinue = true;
while (index < resultSetIndex.Value)
{
if (!rdr.NextResult())
{
bContinue = false;
break;
}
index++;
}
if (!bContinue)
throw new Exception("Unable to read result sets.");
.......
List<SqlMetaData> metadataList = new List<SqlMetaData>();
for (int i = 0; i < rdr.FieldCount; i++)
{
string dbTypeName = rdr.GetDataTypeName(i);
SqlMetaData metadata;
if (dbTypeName.ToLower().Contains("char"))
metadata = new SqlMetaData(rdr.GetName(i), (SqlDbType)Enum.Parse(typeof(SqlDbType), dbTypeName, true), 50);
else
metadata = new SqlMetaData(rdr.GetName(i), (SqlDbType)Enum.Parse(typeof(SqlDbType), dbTypeName, true));
metadataList.Add(metadata);
}
SqlDataRecord record = new SqlDataRecord(metadataList.ToArray());
object[] values = new object[rdr.FieldCount];
if (rdr.HasRows)
{
SqlContext.Pipe.SendResultsStart(record);
while (rdr.Read())
{
rdr.GetValues(values);
record.SetValues(values);
SqlContext.Pipe.SendResultsRow(record);
}
SqlContext.Pipe.SendResultsEnd();
}
这篇关于从返回多个结果集的存储过程插入临时表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!