November 20, 2013 at 8:20 am
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
November 20, 2013 at 5:49 pm
You can try to see if this will work for you.
create procedure uspDataGet
as
select Code = 'aswe'
union
select Code = 'hgtr'
GO
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
GO
select 'testdata'
----------------------------------
declare @Audit bit = 1
create table #tblData (Code char(10))
insert into #tblData
exec uspDataTEST @Audit = @Audit
select * from #tblData
GO
drop table #tblData
drop procedure uspDataTEST;
DROP procedure uspDataGet;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply