Clever Geek Handbook

Insert into table from stored procedure with different versions

There is a stored procedure sp

and a table variable@tbl 

Insert into @tbl
  Exec sp

The penalty above is initially executed.

We changed the output of the stored procedure and added additional output columns. The above insert statement does not work with the second version of the stored procedure.

The above SQL should work with version 1 sp

and version 2sp

What can be done?

SQL Server does not provide the ability to specify column names when doing an insert from sp

output

+3


source to share


1 answer


The only way to do this is to use EXECUTE

WITH RESULT SETS

to define the exact columns to return from the stored procedure. This will give you the ability to specify the column names to insert into the table variable from the output of the stored procedure.

Insert into @tbl(col1, col2, col3) EXECUTE sp
WITH RESULT SETS (
    (col1 INT, 
     col2 INT,
     col3 nvarchar(50))
)


For more information see here .

+6


source






More articles:


All Articles