SQL Server ed ALM su database


Il blog di Alessandro Alpi
Archivio Posts
Anno 2018

Anno 2017

Anno 2016

Anno 2015

Anno 2014

Anno 2013

Anno 2012

Anno 2011

Anno 2010

Anno 2009

Anno 2008

Anno 2007

Anno 2006

INSERT-EXEC innestati, limiti e (imp)possibili workaround

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)
In realtà quindi questa restrizione non può essere evitata così semplicemente. Possiamo pensare ad altre tecniche, come condividere i dati con oggetti temporanei (link molto interessante qui: "how to share data between stored procedures").
Vi sono anche alcuni item su connect che sono stati chiusi con la promessa di una miglioria nel futuro:

Stay Tuned! 

Categoria: Transact-SQL
lunedì, 14 ott 2013 Ore. 09.21
Statistiche
  • Views Home Page: 485.524
  • Views Posts: 875.181
  • Views Gallerie: 500.849
  • n° Posts: 484
  • n° Commenti: 273



















Copyright © 2002-2007 - Blogs 2.0
dotNetHell.it | Home Page Blogs
ASP.NET 2.0 Windows 2003