Insert result of EXEC into table parameter

Question

In a stored procedure, I have an EXEC statement; I want to store its results into a table.

First I create table parameter as:

DECLARE @MyTable AS TABLE ( [Item1] INT, [Item2] DECIMAL )

Then I try to insert values as:

INSERT INTO @MyTable EXEC [storedProcedure] @testitem = @testitem

My question: is this the right way to do that? Or do I need to use a dynamic query?

Because I read similar questions like this and they suggest to use a dynamic query.

From the link you provided this question and answer relate to the 2000 version of SQL Server. In later versions, the restriction on INSERT INTO @table_variable ... EXEC ... were lifted and so it doesn't apply for those later versions. — Hadi
– Hadi, Commented Mar 6, 2019 at 18:47
You are using a current version of sql server, so the link is irrelevant. The table variable restriction no longer applies (and has not applied for many years). Short answer - your code is fine though some would argue that you should always specify the column list in a insert statement. Laziness is not a good habit for a developer. — SMor
– SMor, Commented Mar 6, 2019 at 22:04

lije · Accepted Answer · 2019-03-06 19:04:55Z

You have to use a dynamic query.

DECLARE @Sql AS VARCHAR(MAX); SET @Sql='EXEC storedProcedure @testitem = @testitem' INSERT INTO @MyTable ([Item1], [Item2]) EXEC(@Sql) SELECT * FROM @MyTable;

Asserting the above, I've been just tempted to read further... and found this older thread. I bet, it is helpful to analyze it further. Insert results of a stored procedure into a temporary table

As OP's link states, the restriction against table variables was removed long ago (2008 maybe). There is no requirement or actual need (in this case) to use dynamic sql.

Collectives™ on Stack Overflow

Insert result of EXEC into table parameter

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related