Nested INSERT EXEC
Posted on 2004-09-09
I have run into an insert exec nesting problem and I would like to find out how to go around this. The scenario of occurrence is as follows: -
Stored procedure A has the following line which executes : spGetALLUserGroupData
INSERT INTO #ALLFacts1 EXECUTE ('spGetALLUserGroupData ''' + @UserGroupName + ''' ,''Factory''')
The temp table #ALLFacts1 and the corresponding variables are all set up correctly.
Within the spGetALLUserGroupData stored procedure, the following statement is generating the nesting error: -
INSERT INTO #ALLFacts
EXECUTE ('SELECT DISTINCT factory_cd, factory_desc
FROM LINEN_FACTORY, CUSTOMER
WHERE customer.serv_site = linen_factory.factory_cd
AND customer.ngrp_cd IN (SELECT ngrp_cd FROM user_group_group
WHERE user_group_id = ' + @UGrpId + ')')
Any solutions please?