| Author |
 Topic  |
|
|
azmi
Starting Member
Malaysia
37 Posts |
 Posted - 11/11/2005 : 20:05:58
|
hi guy...
i need your assist to overcome error message 'an INSERT EXEC statement cannot be nested' when i conmpile my sproc. Below is sproc (simplified) to explained the error message looks like..
(1)
------------------------------
create procedure HTX_ar_rep_ledger_test
@company_no GLCOMPANY,
@company_locn GLLOCN,
@languageid CMN_LANGID = 1,
@cust_no cmn_cust_ven_code,
@cust_no1 cmn_cust_ven_code,
@from_date char(10),
@to_date char(10))
as
begin
..
..
insert into #aged_calc
EXEC salesdb..HTX_Aged_Receivable_Rpt @company_no,@company_locn, @languageid, @cust_no,@cust_no,@to_date
..
..
end
(2)
-----------------------------------
create procedure HTX_Smt_of_Acct_by_Role
( @company_no GLCOMPANY,
@company_locn GLLOCN,
@languageid CMN_LANGID = 1,
@cust_no cmn_cust_ven_code,
@cust_no1 cmn_cust_ven_code,
@from_date char(10),
@to_date char(10))
AS
begin
..
..
..
if (condition)
begin
raiserror ('You Dont Have An Authorised To View This Customer!!!')
end
else
begin
INSERT INTO as_mie_tmp
EXEC dbo.HTX_ar_rep_ledger_asmie @company_no,@company_locn,@languageid,@cust_no,@cust_no1,@from_date,@to_date
end
end
---------------
-in sproc (1) i already use insert exec statement.
-in sproc (2) i use again insert exec statement cause in want to populate result to as_mie_tmp tmp table
do you have any idea and other option how to workaroud with this sproc to avoid this error.Thanks..
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 11/11/2005 : 20:13:19
|
does your as_mie_tmp table have the same columns as your sproc returns?
Go with the flow & have fun! Else fight the flow  |
 |
|
|
azmi
Starting Member
Malaysia
37 Posts |
Posted - 11/11/2005 : 20:16:52
|
ops.sory there is some mistake sproc no (2). the sproc look like this...
create procedure HTX_Smt_of_Acct_by_Role
( @company_no GLCOMPANY,
@company_locn GLLOCN,
@languageid CMN_LANGID = 1,
@cust_no cmn_cust_ven_code,
@cust_no1 cmn_cust_ven_code,
@from_date char(10),
@to_date char(10))
AS
begin
..
..
..
if (condition)
begin
raiserror ('You Dont Have An Authorised To View This Customer!!!')
end
else
begin
INSERT INTO as_mie_tmp
EXEC HTX_ar_rep_ledger_test @company_no,@company_locn,@languageid,@cust_no,@cust_no1,@from_date,@to_date
end
end
the sproc (2) exec again the sproc (1) within sproc..I hope u understand...thanks.. |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 11/11/2005 : 20:21:18
|
ohh i see your calling the first sproc in the second sproc...
hmm... i didn't know that you can't nest them like that...
maybe you could use global temp tables in your HTX_Aged_Receivable_Rpt sproc. that way you wouldn't need to do insert into exec
Go with the flow & have fun! Else fight the flow |
|
|
|
azmi
Starting Member
Malaysia
37 Posts |
Posted - 11/11/2005 : 20:22:47
|
| thnks spirit1 for reply.yes as_mie_tmp temp table return same column as sproc returns. I've read read some article that we can use other method like 'openquery' or using dynamic sql.Do u know about that.. |
|
|
|
azmi
Starting Member
Malaysia
37 Posts |
Posted - 11/11/2005 : 20:29:33
|
| how to use global tmp table in my HTX_Aged_Receivable_Rpt sproc. izzit i have to change # table to permenent tmp table? |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 11/11/2005 : 20:37:12
|
use ##temp instead of #temp
##temp's get dropped when all of the connections that use them aren't closed.
they're preety usefull for your problem. and you don't need openquery.
Go with the flow & have fun! Else fight the flow |
|
|
|
azmi
Starting Member
Malaysia
37 Posts |
Posted - 11/11/2005 : 20:41:26
|
| thanks for your information. i will try your suggestion... |
|
|
|
azmi
Starting Member
Malaysia
37 Posts |
Posted - 11/18/2005 : 03:15:03
|
| i had tried using ##tmp as you are suggested, but the error message still persist. Can i have an example stored proc regarding with this problem.. |
|
|
|
azmi
Starting Member
Malaysia
37 Posts |
Posted - 11/21/2005 : 03:17:59
|
| hi guys, i need your expertise to solve this problem. Anyone can come out with best solution. I 've spend a week try to solve but still not found the solution. Your attention is much appreciated. |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 11/21/2005 : 04:14:07
|
use northwind
go
create proc test1
as
select orderid, customerid, employeeid from ##temp2
go
create proc test2
as
create table ##temp2 (orderid int , customerid char(5), employeeid int)
insert into ##temp2 exec test3
go
create proc test3
as
select top 100 orderid, customerid, employeeid from orders
go
exec test2
exec test1
go
drop proc test1, test2, test3
drop table ##temp2
go
Go with the flow & have fun! Else fight the flow |
|
|
| |
Topic |
|
an INSERT EXEC statement cannot be nested
