An INSERT EXEC statement cannot be nested.

[osjohnm]

Hi all

I understand why I'm getting this error but do I overcome it?

I have a stored procedure called IAM_CAC_CHART(will supply code below) which will be used to pass data to the developers to generate a pie report. The proc uses a cursor as well as 2 temp tables, 1 outside the cursor and another within. Within the cursor i use dynamic sql to execute another proc called IAM_CAC. This is used in an INSERT INTO EXEC(@sql) to store the result returned from IAM_CAC into the 1 temp table.

The catch is that IAM_CAC also executes dynamic sql to exec another proc and store the result in a temp table. IAM_CAC calls a proc called IAM_TOTAL_BASE_VALUE as it has to do calculations using the result returned.

Since SQL Server doesn't support nested INSERT EXEC statements I've run into a wall.

The called procs are mainly used for a very important line graph which displays TCO figures, base price, condition assessment code for tagged assets across a certian time period. I want to avoid changing the called procs at all costs as they work with formulas, calculations, cursors etc and took awhile to get bedded down.

Here is the code:

CREATE procedure IAM_CAC_CHART @costcentre varchar(20)
as

declare @tagno varchar(20)
declare @desc varchar(200)
declare @assetno varchar(20)
declare @serialno varchar(50)
declare @type varchar(20)

create table #piechart
(tag_no varchar(20),
description varchar(200),
asset_no varchar(20),
serial_no varchar(50),
type varchar(20),
cac int)

declare chart_cursor cursor for
select a.tag_no, a.description, a.asset_no, a.serial_no, 'PRIMARY'
from asset a, tag_record tr
where a.cost_centre = @costcentre
and a.tag_no is not null
and a.asset_id = tr.asset_id
and tr.rel_indic is null
order by a.tag_no

open chart_cursor
fetch chart_cursor into @tagno, @desc, @assetno, @serialno, @type
while @@fetch_status = 0
begin

create table #piecac
(cac money)

declare @value money
declare @sql varchar(1000)
set @sql = 'exec iam_cac '+'"' +@tagno+'"'

insert into #piecac
exec(@sql)

set @value = (select * from #piecac)

insert into #piechart (tag_no, description, asset_no, serial_no, type, cac)
select @tagno, @desc, @assetno, @serialno, @type, @value

fetch chart_cursor into @tagno, @desc, @assetno, @serialno, @type
end

close chart_cursor
deallocate chart_cursor

select * from #piechart
GO

I'm also getting errors about the temp table already exists. I've been microsoft support and read the articles there but I didn't find a solution.

Is there away to change IAM_CAC_CHART to do what I need without changing the called procs or only changing the called procs slightly?

Any help will be appreciated greatly!

John

 

[Warlocks]

John,

A couple of things I see at first glance is that you seem to have an insert statement that doesn't insert any values.

insert into #piecac
exec(@sql)

If the results from the exec statement are to contain the values for the insert, build up a string containing "insert into #piecac" and add the values onto the end and then do another exec statement to run the string.

You also asked the question as to why you were getting table already exists for the temporary table. This will be because when you create a temporary table, it creates it in the TEMP database. The table will stay there until SQLServer is next restarted as this is when the TEMP database is flushed. You can use an IF statement to determine whether the table already exists and if so either delete it first (in order to re-create it) or do nothing and bypass the create table statement.

Hope this helps,

Woody.

 

[osjohnm]

Warlock

The @sql contains a sql statment that executes a stored procedure. The insert is used to store the result returned from the called procedure. That part is fine as I often execute dynamic sql. The problem is that the called procedure is called using INSERT EXEC but the called procedure calls another procedure in the same way. Thats the nesting its complaining about.

Also if I'm not mistaken local temporary tables (#) are only available thru the current session. Once the session is stopped the temp table is dropped automatically. Because of the error I think that the session is not being closed and therefore I get the error.

John