EXECUTE cannot be used as a source when inserting into a table variable.

Hi Experts,

I am getting an error in T-SQL while I am trying to get the results of one stored procedure into a table.

The code that gives error is,

DECLARE @RetTable TABLE(ReportHCEntityID int, DataElementID int, ElementValue varchar(50))

INSERT @RetTable
      EXEC dbo.spSelectReportDataForMetric @MetricID, @DataElement1, @DataElement2

Where dbo.spSelectReportDataForMetric is another stored procedure. Does this mean I cannot do something like this. Do I have to use user defined functions?

I saw an example in SQL Server Online Books. I am trying to locate that example again. But I am pretty sure example was inserting result of execute into the table.

Thanks a lot

Dharmesh
LVL 6
dharmesh_amityAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
acampomaConnect With a Mentor Commented:
nope, Posted in online help
cannot insert stored proc results into a table var

see under table variable in online help under remarks:
A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch in which it is declared.
Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table may not be used in the following statements:

INSERT INTO table_variable EXEC stored_procedure

SELECT select_list INTO table_variable statements
0
 
SQL_StuCommented:
Why don't you have @table as a parameter to your spSelectReportDataForMetric stored proc?  This should solve your problem.
0
 
dharmesh_amityAuthor Commented:
Oh! can you have that. I didn't know I will check out an example.

Thanks
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
dharmesh_amityAuthor Commented:
If I write this I get error

CREATE PROCEDURE dbo.spSelectReportDataForMetric
@MetricID int,
@DataElement1 int,
@DataElement2 int = -1,
@RetTable TABLE = NULL
AS



Can you show me an example where I can pass table as a parameter to Stored Procedure

Dharmesh
0
 
SQL_StuConnect With a Mentor Commented:
Sorry, I was thinking that you would have already created the physical table.

ie. pass the table name as a string to the SP.

Anyway, why do you want to use variables at all for your table?  If you need to store the data why don't you use temporary tables?
0
 
dharmesh_amityAuthor Commented:
I think table variables are more efficient and I have many stored procedures already written with this pattern. I would not like to change this pattern, I would rather use User Defined Function. I thought I saw an example but I guess I was wrong as "acampoma" said.

I can still do it with udf. I guess I will be going that way.

Thank a lot,

Dharmesh
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.