I try to select a store procedure in SqlExpress2005 which inside store procedure execute another store procedure,
When I select it but it prompt error messages "An INSERT EXEC statement cannot be nested.".
In Fire bird /Interbase store procedure we can nested. Below are the code;
declare @.dtReturnData Table(doccode nvarchar(20), docdate datetime, debtoraccount nvarchar(20))
Insert Into @.dtReturnData
Exec GetPickingList 'DO', 0, 37256, 'N', 'N', 'YES'
Select doccode, docdate, debtoraccount
From @.dtReturnData
Inside the GetPickList It will do like this, but most of the code I not included;
ALTER PROCEDURE GETPICKINGLIST
@.doctype nvarchar(2),
@.datefrom datetime,
@.dateto datetime,
@.includegrn char(1),
@.includesa char(1),
@.includedata nvarchar(5)
AS
BEGIN
declare @.dtReturnData Table(doccode nvarchar(20),
docdate datetime,
debtoraccount nvarchar(20))
IF (@.DOCTYPE = 'SI')
BEGIN
Insert Into @.dtSALESINVOICEREGISTER
Exec SALESINVOICEREGISTER @.DateFrom, @.DateTo, @.IncludeGRN, @.IncludeSA, @.IncludeData
END
ELSE
BEGIN
Insert Into @.dtDELIVERYORDERREGISTER
Exec DELIVERYORDERREGISTER @.DateFrom, @.DateTo, @.IncludeGRN, @.IncludeSA, @.IncludeData
END
Select doccode,docdate,debtoraccount From @.dtReturnData
END
So how can I select a nested store procedure? can someone help me
Jeremy,
This is a problem that comes up from time to time in SQL Server. My first suggestion when this comes up is to look at both points in which you are using the INSERT ... EXEC syntax. See if it is possible to convert at least one of the procedures into a user defined function. There is a "Plan C" for this but it is not nearly as clean as the option of converting to a function (if possible).
Also, for future keep in mind that it is a good idea to consider using functions -- especially inline functions -- instead of stored procedure when it is the intent to load the output from a procedure into a table.
See if SalesInvoiceRegister and DeliveryOrderRegister can be converted to functions.
Kent
No comments:
Post a Comment