An INSERT EXEC statement cannot be nested.

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)

An INSERT EXEC statement cannot be nested.

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2013-11-20 : 10:16:58

Hi,
I am using sql server 2008 R2
Can you help me to solve this error please?
An INSERT EXEC statement cannot be nested.

------------------------------
create procedure uspDataGet
as
select Code = 'aswe'
union
select Code = 'hgtr'

-------------------------------

A simplified stored proc.
------------------------------
create procedure uspDataTEST

@Audit bit = 0

as

declare @tblResult table
(
Code char(10)
)

--populate @tblResult with the returned data from exec uspDataGet

insert into @tblResult
exec uspDataGet '' --this stored proc. retrieves one column

if (@Audit = 1)
begin
select * from @tblResult

return
end

select 'testdata'
----------------------------------

declare @Audit bit = 1
create table #tblData (Code char(10))

insert into #tblData
exec uspDataTEST @Audit = @Audit

select * from #tblData
drop table #tblData
-----------------------------------
Because of business rules, the stored proc. uspDataTEST should not be changed.
I know that this error is given because in the calling stored proc. there is insert into ...exec storedproc
But not sure how to solve this error when I call the stored proc.

Thank you

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-20 : 10:44:09

This is a limitation of the insert..exec construct - that you cannot nest them. If you are not able to make changes to the stored procedure, and must insert the data into a table, another alterative would be to use OPENQUERY

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2013-11-25 : 04:57:08

How do I use open query in my example please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-25 : 05:14:17

quote:
Originally posted by arkiboys

How do I use open query in my example please?

see example 2 here

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources