Stored procedure & metadata

  • Article

Può capitare la necessità di conoscere, a runtime, lo schema (i metadati) del resultset uscente da una stored procedure.

Recuperare queste informazioni può essere utile, ad esempio, in tutti quegli scenari in cui lo schema varia al variare del codice (magari perchè dinamicamente si vanno a generare, sulla base di parametri e/o dati, set differenti).

Prima di SQL Server 2012non era semplicissimo risolvere questa problematica.

Adesso, tramite la stored procedure sp_describe_first_result_set, diventa una banalità:

 EXEC sp_describe_first_result_set 
    @tsql = N'exec [AdventureWorks2012].[dbo].[uspGetEmployeeManagers] 1';

image

 

Per le versioni precedenti di SQL Server la soluzione era leggermente più complessa (con la necessità, tra le altre cose, ti utilizzare il comando OPENROWSET e di attivare la configurazione di “Ad Hoc Distributed Queries”):

 /*
EXEC sp_configure 'show advanced options' , 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries' , 1;
RECONFIGURE;
GO
*/


IF OBJECT_ID( N'tempdb..#t', N'U' )IS NOT NULL
    BEGIN
        DROP TABLE #t;
    END;

/* please type your server... */
SELECT P.* INTO #t
  FROM OPENROWSET
       ( 
       'SQLNCLI' , 
       'Server=.\SQL2012;Trusted_Connection=yes;' , 
       'exec [AdventureWorks2012].[dbo].[uspGetEmployeeManagers] 1' 
       ) AS P
  WHERE 1 = 0;

SELECT c.name as [column name], 
       ( 
         SELECT t.name
           FROM sys.types t
           WHERE 
          t.user_type_id = c.system_type_id AND 
          is_user_defined = 0 
       ) AS [column type] , 
       c.max_length
  FROM tempdb.sys.columns c
  WHERE object_id = OBJECT_ID( N'tempdb..#t' , N'U' );

image