Sql Server: inserire la tabella restituita da una stored procedure in una tabella temporanea

A volta mi càpita di “diventare matto” per cercare du risolvere un problema apparentemente insolubile; spesso la soluzione si rileva di una semplicità direttamente proporzionale alla difficoltà do trovare informazioni al riguardo: in questo caso, stavo cercando di inserire in una tabella i dati restituiti da una stored procedure. Cominciamo quindi a definire una stored procedure che come risultato della sua elaborazione restituisca una tabella:

USE [Turro];
GO

IF NOT EXISTS ( SELECT * FROM sys.objects WHERE [Object_Id] = Object_Id( 'dbo.TestInsertIntoTempTable' ) )
    EXECUTE ( 'CREATE PROCEDURE [dbo].[TestInsertIntoTempTable] AS BEGIN SET NOCOUNT ON; END' );
GO

ALTER PROCEDURE dbo.TestInsertIntoTempTable
(
    @rows int
)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP (@rows)
        [database_id], [name], [state_desc], [compatibility_level], [collation_name], [create_date]
    FROM [msdb].[sys].[databases]
    ORDER BY [Name];

    SET NOCOUNT OFF;
END
GO

A questo punto si vorrebbe poter fare qualcosa del tipo:

DECLARE @rows int;
SET @rows = 10;

CREATE TABLE #MyTempTable
(
    [Id] int
  , [Name] nvarchar(255)
  , [Status] nvarchar(20)
  , [Version] char(4)
  , [Collation] nvarchar(255)
  , [Created] datetime
);

INSERT INTO #MyTempTable
    EXECUTE dbo.TestInsertIntoTempTable @rows;

Semplice, direi, e funziona … a volte!

Peccato che – come ha evidenziato Erland Sommarskog nel suo post How to Share Data between Stored Procedures – questa soluzione presenti diversi problemi:

1. questa soluzione non può essere nidificata, per una precisa limitazione di Sql Server;
2. se si modifica il recordset restituito dalla stored procedure, occorre modificare anche il codice chiamante;
3. a causa della INSERT (che implica comunque una transazione per conto suo), la procedura viene eseguita nel contesto di una transazione, cosa che può creare ulteriori problemi (se la procedure accede ad un linked server occorre avere abilitate le Distributed Transactions, alcune procedure di sistema non funzionano sotto transazione, la transazione potrebbe allungare i tempi dei lock creando rischi di problemi di concorrenza);
4. diventa più complicata la gestione degli errori tramite la TRY...CATCH.

Per risolvere questi problemi possiamo sfruttare la funzione OpenRowSet, modificando l’ultima istruzione:

INSERT INTO #MyTempTable
    SELECT *
    FROM OPENROWSET ( 'SQLNCLI'
                     , 'Server=TURRINI;Trusted_Connection=yes;'
                     , 'EXEC Turro.dbo.TestInsertIntoTempTable @rows = 5'
                     );

Questa soluzione, oltretutto, permette di collegarsi a qualsiasi linked server definito; vi sono però due cose da ricordare

1. nella EXEC dobbiamo ricordarci di indicare anche il nome del catalogo o database, che dir si voglia;
2. soprattutto, questa sintassi funzione se e solo se è stata abilitata l’opzione 'Ad Hoc Distributed Queries'.

Nel caso l’opzione non sia attiva, bisogna quindi riconfigurare Sql Server tramite apposita stored procedure di sistema:

sp_configure 'Show Advanced Options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

Si noti che dopo ogni sp_configure è necessario eseguire l’istruzione RECONFIGURE. Volendo poi ripristinare i valori iniziali, sarà sufficiente reimpostare il valore 0 (ZERO), avendo l’accortezza però di invertire tra loro l’ordine di esecuzione delle due sp_configure:

sp_configure 'Ad Hoc Distributed Queries', 0;
GO
RECONFIGURE;
GO
sp_configure 'Show Advanced Options', 0;
GO
RECONFIGURE;
GO