There is a stored procedure sp and a table variable @tbl
有一个存储过程sp和一个表变量@tbl
Insert into @tbl
Exec sp
Above works fine initially.
最初上面没问题。
We changed the stored procedure output and added additional output columns. The insert statement above fails with 2nd version of the stored procedure.
我们更改了存储过程输出并添加了额外的输出列。上面的insert语句在存储过程的第二个版本中失败。
The above SQL needs to work with version 1 of sp and version 2 of sp
上述SQL需要与sp的版本1和版本2配合使用
What can be done?
可以做些什么?
SQL Server doesn't provide the option to specify the column names while doing the insert from sp output
SQL Server在执行从sp输出的插入操作时不提供指定列名的选项
1 个解决方案
#1
6
The only way to do it is to use the EXECUTE WITH RESULT SETS to determine the exact columns to return from the stored procedure. This will give you the option to specify the column names to insert into the table variable from the stored procedure output.
惟一的方法是使用带有结果集的EXECUTE来确定从存储过程返回的确切列。这将使您可以选择指定列名称，以便从存储过程输出中插入到表变量中。
Insert into @tbl(col1, col2, col3) EXECUTE sp
WITH RESULT SETS (
(col1 INT,
col2 INT,
col3 nvarchar(50))
)
For more information, see here.
有关更多信息，请参见这里。
#1
6
The only way to do it is to use the EXECUTE WITH RESULT SETS to determine the exact columns to return from the stored procedure. This will give you the option to specify the column names to insert into the table variable from the stored procedure output.
惟一的方法是使用带有结果集的EXECUTE来确定从存储过程返回的确切列。这将使您可以选择指定列名称，以便从存储过程输出中插入到表变量中。
Insert into @tbl(col1, col2, col3) EXECUTE sp
WITH RESULT SETS (
(col1 INT,
col2 INT,
col3 nvarchar(50))
)
For more information, see here.
有关更多信息，请参见这里。