0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jonathan
  • 601
  • 9
  • 26
  • 3
    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 Mar 06 '19 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 Mar 06 '19 at 22:04

1 Answers1

0

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

lije
  • 420
  • 2
  • 15
  • 1
    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. – SMor Mar 06 '19 at 22:11