For what it is worth, we have gotten around this by having the called SP return a text string containing the offending SQL code to the calling SP, then executing the code WITHIN the calling SP, rather than calling on the second SP to do the EXEC.

Further, so that the subordinate SP can be called directly for its data output, or called by another SP for further use of the data, we configure it with an output variable as a switch. If we call it with the output variable NULL, it EXECUTES the internal SQL and passes back the data (e.g., to an ASP.NET request). If we need to use it in another SP, we we pass an output variable to it, and get back the SQL text that we then execute inside the other SP.

Here is a simple example which uses a table tblDevices which has fields Device_Id (int) and FirstName (varchar(30)) amongst other things. There is a record with Device_Id = 10275.

Subordinate SP (@r is the "switch")

CREATE PROCEDURE [dbo].[sp_TestNested1] (@Device_ID Int, @r VARCHAR(MAX) out)

AS

BEGIN

DECLARE @sSql VARCHAR(MAX)

SELECT @sSql = 'DECLARE @Table TABLE(FirstName VARCHAR(30))

BEGIN

INSERT INTO @Table(FirstName) (SELECT FirstName FROM tblDevices WHERE Device_Id = ' + CAST(@Device_Id AS VARCHAR(10)) + ') END

SELECT * FROM @Table'

END

BEGIN

IF @r IS NULL

BEGIN

EXEC(@sSql)

END

ELSE

BEGIN

SELECT @r = @sSql

END

END

Calling SP

CREATE PROCEDURE [dbo].[sp_TestNested2] (@Device_ID Int)

AS

BEGIN

Declare @r VARCHAR(Max)

EXEC dbo.sp_TestNested1 10275, @r OUTPUT

EXEC(@r)

END

--------------------------------

So, if we

EXEC dbo.sp_TestNested1 10275,null, it executes the SQL and we get back the results

If we EXEC dbo.sp_TestNested2 10275, it passes the @r switch, gets back the SQL in @r, and executes the dbo.sp_TestNested1 code internally.

At the end of the day, we have a single object in dbo.sp_TestNested1 that can either be executed itself, or used by any number of other SPs that need to do something with its results without duplicating the dbo.sp_TestNested1 code.