asked on

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

ASKER CERTIFIED SOLUTION

acampoma

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SQL_Stu

Why don't you have @table as a parameter to your spSelectReportDataForMetric stored proc? This should solve your problem.

dharmesh_amity

ASKER

Oh! can you have that. I didn't know I will check out an example.

Thanks

dharmesh_amity

ASKER

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

SOLUTION

SQL_Stu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dharmesh_amity

ASKER

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