Error in procedure
-
When executing a procedure i get an error as
An INSERT EXEC statement cannot be nested.
Warning: Null value is eliminated by an aggregate or other SET operation.
The error shows up in the procedure where i am executing another procedure and inserting the result set in a #temp table like
insert into #temp exec new proc @param
The #temp table is already created before i am doing an insert. Any thoughts or ideas on this. any help will be greatly appreciated.
TIA
-
Can we see the proc code?
-
Heres the proc. error shows up on the lines marked in RED
CREATE PROCEDURE dbo.XXXXX
(@rpttype varchar(3),
@CorrID char(4), @prsnid char(6) = NULL) -- for logging purposes only
AS
SET NOCOUNT ON
BEGIN
IF @rpttype NOT IN ('S','D')
BEGIN
RAISERROR ('ERROR: Report type must be S or D', 16, 1)
RETURN
END
IF @CorrID NOT LIKE '__A1'
BEGIN
RAISERROR ('ERROR: Please enter valid Correspondent ID', 16, 1)
RETURN
END
--add logging variables and set initial value
DECLARE @parm_start datetime
SET @parm_start = getdate()
DECLARE @Rows int
DECLARE @parm_list_values varchar(8000)
-- get list of entities
create table #tmpgetety01(
[ety_id] [smallint] NOT NULL)
declare @etyid smallint
set @etyid = (select ety_id from pfe_lku(nolock) where pfe_lku_lku = @corrid and pfe_lku_lku_cod = 'ece' and ts = 0)
--select @etyid 'etyid'
insert into #tmpgetety01 exec css_ety_id_children @etyid
--insert into tmpgetety01 values (@etyid)
select * from #tmpgetety01
create table #tmpaccts(
[acc_id] [char](9) NULL,
[cat_id] [char](3) NULL,
[cat_seq_nbr] [tinyint] NULL,
[sisaccnum] [varchar](40) NULL,
[ety_id] [smallint] NULL,
[office] [varchar](40) NULL,
-- [type] [varchar](25) NULL,
[acc_acc_reg_cod] [char](3) NULL,
[acc_res_res_cod] [varchar](20) NULL,
[cash_bal] [numeric](18,2) NULL,
[margin_bal] [numeric](18,2) NULL,
[short_bal] [numeric](18,2) NULL,
[cash_pos] [numeric](18,2) NULL,
[margin_pos] [numeric](18,2) NULL,
[short_pos] [numeric](18,2) NULL,
[Totals] [numeric](18,2) NULL
 
INSERT INTO #tmpaccts
SELECT acc_id, cat_id, cat_seq_nbr, '', AA.ety_id , '', AA.acc_acc_reg_cod, '', 0, 0, 0, 0, 0, 0, 0
FROM PROD..acc_acc AA (NOLOCK)
JOIN #tmpgetety01 T on T.ety_id = AA.ety_id
WHERE AA.ts = 0
create clustered index ix_tmpaccts on #tmpaccts (acc_id, cat_id, cat_seq_nbr)
-
The error is pretty clear:
You Can not nest insert exec commands
In other words: if p1 uses insert exec in it the following is not allowed
create proc p2 as
insert exec p1
* Noel -
And I guess this is why >>
create proc p2 as
insert exec p1
GO
create proc p1 as
insert exec p2
GO
-
Just check the procedure:
css_ety_id_children
If if uses an insert exec in it you can't nested on the outer like:
insert into #tmpgetety01 exec css_ety_id_children ...
* Noel -
Thanks Noel. You have always been a great help...
-
Happy to Help
* Noel
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply