select from stored proc - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

select from stored proc

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-09-15 : 09:59:10

Stored procedure (sp) uses other stored procedures to returns fields with data.
The data returned is something like:

field1, field2, field3, field4...

In a separate query analyser, I would like to place the result from sp into a table variable with the same fields and datatypes. But the error is:
An INSERT EXEC statement cannot be nested.

This is what I am running:

declare @tblTest table
(
field1 varchar(10) null,
field2 float,
field3 float,
field4 float
)

insert @tblTest
exec sp @Code = 'auto', @Net = 1

select * from @tblTest

Any thoughts how to solve this please?
Thanks

Kristen
Test

22859 Posts

Posted - 2011-09-15 : 10:26:46

Can't do insert @tblTest from an EXEC. You can insert into a #TempTable though.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-09-15 : 10:45:20

Hi,
1- It is now using temp table
2- Not all the records are inserted. It says:
An INSERT EXEC statement cannot be nested.

Kristen
Test

22859 Posts

Posted - 2011-09-15 : 10:59:42

Your SP is already doing an INSERT INTO ... EXEC, or its calling a child SProc that is doing that.

Either fix that child Sproc, or use


INSERT INTO #TempTable
SELECT X.*
FROM OPENROWSET('MSDASQL','DRIVER={SQL Server}; SERVER=MyServerName; UID=MyUserID; PWD=MyPassWord’,
'EXEC sp @Code = ''auto'', @Net = 1') AS X

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-09-15 : 11:31:32

Yes, the sp is already doing several insert into tbl exec spa...
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources