Tempo fa ho ricevuto un errore riguardante lo statement INSERT-EXEC. Il problema, ormai conosciuto, è una limitazione del motore di SQL Server, di certo presente dalla versione 2005. Lo statement in oggetto era supportato anche in 2000 ma non ho mai avuto l'occasione di ottenere lo stesso comportamento. Immagino che la limitazione possa essere presente anche su SQL Server 2000.

Scenario

Immaginiamo di avere una stored procedure come questa, che semplicemente torna un resultset:

CREATE PROCEDURE dbo.proc_getdata
AS
 SELECT 1 AS Value
 UNION
 SELECT 2
GO

Ora scriviamo un'altra stored procedure che tramite INSERT-EXEC torna i dati letti e scritti in una tabella d'appoggio:

CREATE PROCEDURE dbo.proc_insert_TopLevel
AS

CREATE TABLE #first (val int);

-- primo insert-exec
 INSERT INTO #first (val)
 EXEC dbo.proc_getdata;

-- torno dati
 SELECT
 val
 FROM
 #first;
GO

Come possiamo vedere, la stored procedure inserisce in una tabella temporanea il risultato di una EXEC. Ripetiamo ora il comportamento tramite una ulteriore stored procedure:

CREATE PROCEDURE dbo.proc_insert_Nested
AS

CREATE TABLE #second (val int);

-- insert-exec innestato (tornerà eccezione)
 INSERT INTO #second (val)
 EXEC dbo.proc_insert_TopLevel;

-- resultset
SELECT
 val
 FROM
 #second;
GO

Provando a chiamare la dbo.proc_insert_Nested, otterremo il seguente errore:

Msg 8164, Level 16, State 1, Procedure proc_insert_TopLevel, Line 9 An INSERT EXEC statement cannot be nested. 

Il suddetto scenario, a dire il vero molto semplice, ha un workaround altrettanto facile da applicare. In questo caso infatti una soluzione possibile è evitare il primo INSERT-EXEC utilizzando una funzione (multistatement on inline) che torni il primo resultset:

CREATE FUNCTION dbo.getdata()
RETURNS TABLE AS
RETURN
(
 SELECT 1 AS id UNION SELECT 2
);
GO

CREATE PROCEDURE dbo.proc_insert_TopLevel
AS

CREATE TABLE #first (val int);

-- rimpiazzato il primo insert-exec
INSERT INTO #first (val)
SELECT Id FROM dbo.getdata()

-- resultset
SELECT
val
FROM
#first;
GO

In questo modo abbiamo risolto il problema. Ma non sempre è così semplice. 

Conclusioni

Ci sono altre realtà, anche più complesse, in cui il workaround proposto non è proprio percorribile. Ad esempio:

• Il data source deve stare all'interno di stored procedure obbligatoriamente (ad esempio se la stored procedure deve cambiare dati al suo interno, cosa che una funzione non può fare)
• Il numero di nidificazioni è maggiore di quello nell'esempio
• Il chiamato non può essere rifattorizzato (ad esempio una stored procedure di sistema o di terze parti)

Stay Tuned!