CODEWITHSUNDEEP
sqlsql-servert-sqlstored-proceduressql-server-2012
bmsqldev
bmsqldev

Reputation: 2735

Insert into a table from a stored procedure with multiple output sets is not working

I need to populate a table from a stored procedure output. I have tried below query.

DECLARE @Result AS TABLE (ouid int, value1 nvarchar(max), value2 nvarchar(max), value3 nvarchar(max))

insert into @Result (value1 ,value2, value3)
    exec [USP_Getofficevalues] 1, NULL, NULL

But I got the errors during execution:

An INSERT EXEC statement cannot be nested.

Column name or number of supplied values does not match table definition.

I found that the error occurs because the stored procedure has multiple result sets, like this:

officedetails
-------------
500000

value1
-----
1000

value2
-----
500

value3
-----
800

How to handle the above scenario? Thanks for the help.

Upvotes: 1

Views: 794

Answers (2)

Squirrel
Squirrel

Reputation: 24763

Since the returned result sets are all single column and same type, you can declare a table variable and store the result:

declare @r table
(
    id int identity,
    value int
)

insert into @r(value)
exec [USP_Getofficevalues] 1, NULL,NULL

After that you just need to transpose @r into your @result table.

Upvotes: 4

Abdul Rehman Sayed
Abdul Rehman Sayed

Reputation: 6672

Check your procedure USP_Getofficevalues.

I think it is using Insert into inside which is not allowed if you are using it as insert while calling.

Upvotes: 1

Related Questions