Restituire dati da una stored procedure

Si applica a:SQL Serverdatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsPiattaforma di strumenti analitici (PDW)

Sono disponibili tre modalità per la restituzione di dati da una procedura a un programma chiamante: i set di risultati, i parametri di output e i codici restituiti. Questo articolo offre informazioni sui tre approcci.

Restituire dati tramite i set di risultati

Se si include un'istruzione SELECT nel corpo di una stored procedure (ma non SELECT ... INTO o INSERT ... SELECT), le righe specificate dall'istruzione SELECT verranno inviate direttamente al client. Per set di risultati di grandi dimensioni, l'esecuzione della stored procedure non passa all'istruzione successiva fino a quando l'intero set di risultati non è stato inviato al client. Per set di risultati di piccole dimensioni, i risultati vengono sottoposti a spooling per la restituzione al client, mentre l'esecuzione prosegue. Se durante l'esecuzione della stored procedure vengono eseguite più istruzioni SELECT di questo tipo, vengono inviati al client più set di risultati. Questo comportamento si applica anche ai batch Transact-SQL nidificati, alle stored procedure nidificate e ai batch Transact-SQL di primo livello.

Esempi di restituzione di dati tramite un set di risultati

Negli esempi seguenti viene usato il database di esempioAdventureWorks2022. Questo esempio mostra una stored procedure che restituisce i valori LastName e SalesYTD per tutte le righe SalesPerson visualizzate anche nella vista vEmployee.

USE AdventureWorks2022;  
GO

IF OBJECT_ID('Sales.uspGetEmployeeSalesYTD', 'P') IS NOT NULL  
   DROP PROCEDURE Sales.uspGetEmployeeSalesYTD;  
GO  

CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD  
AS    
   SET NOCOUNT ON;

   SELECT LastName, SalesYTD  
   FROM Sales.SalesPerson AS sp  
   JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID;
   
   RETURN;
GO 

Restituire dati tramite un parametro di output

Se si specifica la parola chiave output per un parametro nella definizione della procedura, il valore corrente del parametro può essere restituito al programma chiamante dalla procedura, se quest'ultima è disponibile. Per salvare il valore del parametro in una variabile che può essere usata nel programma chiamante, in tale programma deve essere usata la parola chiave output durante l'esecuzione della procedura. Per altre informazioni sui tipi di dati che possono essere usati come parametri di output, vedere CREATE PROCEDURE (Transact-SQL).

Esempi di parametri di output

Nell'esempio seguente viene illustrata una procedura con un parametro di input e uno di output. Il parametro @SalesPerson riceve un valore di input specificato dal programma chiamante. L'istruzione SELECT usa il valore passato nel parametro di input per ottenere il valore SalesYTD corretto. Assegna anche il valore al parametro di output @SalesYTD che restituisce il valore al programma chiamante al termine della procedura.

USE AdventureWorks2022;  
GO  
IF OBJECT_ID('Sales.uspGetEmployeeSalesYTD', 'P') IS NOT NULL  
    DROP PROCEDURE Sales.uspGetEmployeeSalesYTD;  
GO  
CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD  
    @SalesPerson nvarchar(50),  
    @SalesYTD money OUTPUT  
AS    
  
    SET NOCOUNT ON;

    SELECT @SalesYTD = SalesYTD  
    FROM Sales.SalesPerson AS sp  
    JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID  
    WHERE LastName = @SalesPerson;

    RETURN;
GO 

Nell'esempio seguente viene chiamata la procedura creata nel primo esempio e viene salvato il parametro di output @SalesYTD restituito dalla procedura chiamata nella variabile @SalesYTDBySalesPerson.

Esempio:

• Dichiara la variabile @SalesYTDBySalesPerson per ricevere il valore di output della routine.
• Esegue la routine Sales.uspGetEmployeeSalesYTD che specifica un cognome per il parametro di input. Salvare il valore di output nella variabile @SalesYTDBySalesPerson.
• Chiama PRINT per visualizzare il valore salvato in @SalesYTDBySalesPerson.

DECLARE @SalesYTDBySalesPerson money;

EXECUTE Sales.uspGetEmployeeSalesYTD  
    N'Blythe', @SalesYTD = @SalesYTDBySalesPerson OUTPUT;  

PRINT 'Year-to-date sales for this employee is ' +   
    CONVERT(varchar(10),@SalesYTDBySalesPerson);  
GO

Inoltre, è possibile specificare valori di input per i parametri di output quando la procedura viene eseguita. Questa operazione consente alla procedura di ricevere un valore dal programma chiamante, modificare o eseguire operazioni con il valore, quindi restituire il nuovo valore al programma chiamante. Nell'esempio precedente, è possibile assegnare un valore alla variabile @SalesYTDBySalesPerson prima che il programma chiami la procedura Sales.uspGetEmployeeSalesYTD . L'istruzione di esecuzione passa il valore della variabile @SalesYTDBySalesPerson nel parametro di output @SalesYTD. Nel corpo della procedura il valore può quindi essere utilizzato per calcoli che consentono di generare un nuovo valore. Il nuovo valore viene passato di nuovo alla procedura tramite il parametro di output, aggiornando il valore nella variabile @SalesYTDBySalesPerson al termine della routine. Questa funzionalità viene in genere denominata "funzionalità di passaggio per riferimento".

Se si specifica output per un parametro quando si chiama una routine e tale parametro non è stato definito usando output nella definizione della routine, viene restituito un messaggio di errore. Tuttavia, è possibile eseguire una routine con i parametri di output e non specificare output in fase di esecuzione della routine. Non viene restituito alcun errore, ma non è possibile usare il valore di output nel programma chiamante.

Utilizzo del tipo di dati cursor nei parametri di output

Le procedure di Transact-SQL possono usare il tipo di dati cursor solo per i parametri di output. Se per un parametro viene specificato il tipo di dati cursor, nella definizione della routine è necessario specificare per tale parametro entrambe le parole chiave varying e output. Un parametro può essere specificato solo come output, ma se nella dichiarazione del parametro è specificata la parola chiave varying, il tipo di dati deve essere cursor ed è necessario specificare anche la parola chiave output.

Nota

Il tipo di dati cursor non può essere associato a variabili di applicazione tramite API di database come OLE DB, ODBC, ADO e DB-Library. Poiché in un'applicazione è possibile eseguire una routine solo in seguito all'associazione dei parametri di output, le routine con parametri di output di tipo cursor non possono essere chiamate dalle API di database. È possibile chiamare queste routine da trigger, procedure o batch di Transact-SQL solo quando la variabile output di tipo cursor è assegnata a una variabile locale di Transact-SQL di tipo cursor.

Regole per parametri di output di tipo cursor

Quando si esegue la procedura, ai parametri di output di tipo cursor si applicano le regole seguenti:

• Con cursori forward-only, nel set di risultati del cursore sono incluse solo le righe che al completamento della procedura si trovano oltre la posizione del cursore, ad esempio:

  • Un cursore non scorrevole viene aperto in una procedura in un set di risultati di 100 righe denominato RS.

  • La routine recupera le prime 5 righe del set di risultati RS.

  • La procedura restituisce il set di risultati al chiamante.

  • Il set di risultati RS restituito al chiamante include le righe comprese tra la riga 6 e la riga 100 di RS e la posizione del cursore nel chiamante precede la prima riga di RS.

• Con cursori forward-only, se il cursore precede la prima riga quando la procedura è disponibile, l'intero set di risultati viene restituito al trigger, alla procedura o al batch chiamante. Quando viene restituito, il cursore è posizionato prima della prima riga.

• Con cursori forward-only, se il cursore è posizionato oltre l'ultima riga quando la procedura è disponibile, viene restituito un set di risultati vuoto al trigger, alla procedura o al batch chiamante.

  Nota

  Un set di risultati vuoto non equivale a un valore Null.

• Con cursori scorrevoli, quando è disponibile la procedura, tutte le righe del set di risultati vengono restituite al trigger, alla procedura o al batch chiamante. Quando viene restituito, il cursore è nella stessa posizione in cui si trovava durante l'ultima operazione di recupero eseguita dalla procedura.

• Con qualsiasi tipo di cursore, se il cursore è chiuso, viene restituito un valore Null al trigger, alla procedura o al batch chiamante. Questa situazione si verifica anche quando un cursore viene assegnato a un parametro, ma non viene mai aperto.

  Nota

  Lo stato chiuso di un cursore è rilevante solo in fase di restituzione. È possibile, ad esempio, chiudere un cursore nel corso di una procedura, riaprirlo in una fase successiva e restituire il set di risultati di tale cursore al trigger, alla procedura o al batch chiamante.

Esempi di parametri di output di tipo cursor

Nell'esempio seguente viene creata una procedura in cui è specificato un parametro di output @CurrencyCursor che usa il tipo di dati cursore. La procedura viene quindi chiamata in un batch.

Creare innanzitutto la routine che consente di dichiarare e, successivamente, di aprire un cursore nella tabella Currency.

USE AdventureWorks2022;  
GO

IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL  
    DROP PROCEDURE dbo.uspCurrencyCursor;  
GO

CREATE PROCEDURE dbo.uspCurrencyCursor   
    @CurrencyCursor CURSOR VARYING OUTPUT  
AS  
    SET NOCOUNT ON;

    SET @CurrencyCursor = CURSOR FORWARD_ONLY STATIC FOR  
      SELECT CurrencyCode, Name  
      FROM Sales.Currency;  

    OPEN @CurrencyCursor;  
GO  

Eseguire quindi un batch che consenta di dichiarare una variabile locale di cursore, eseguire la procedura per assegnare il cursore alla variabile locale e recuperare le righe dal cursore.

USE AdventureWorks2022;  
GO

DECLARE @MyCursor CURSOR;  

EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;  

WHILE (@@FETCH_STATUS = 0)  
    BEGIN;  
         FETCH NEXT FROM @MyCursor;  
    END;  

CLOSE @MyCursor;  
DEALLOCATE @MyCursor;  
GO    

Restituzione di dati tramite un codice restituito

Una procedura può restituire un valore intero, denominato codice restituito, per indicare lo stato di esecuzione di una procedura. Per specificare il codice restituito per una routine, usare l'istruzione RETURN. Come per i parametri di output, è necessario salvare il codice restituito in una variabile quando la routine viene eseguita per usare il valore del codice restituito nel programma chiamante. La variabile di assegnazione @result del tipo di dati int viene ad esempio usata per archiviare il codice restituito dalla routine my_proc:

DECLARE @result int;

EXECUTE @result = my_proc;
GO

I codici restituiti vengono in genere utilizzati nei blocchi per il controllo di flusso all'interno delle procedure per impostare il valore del codice restituito per ogni possibile situazione di errore. È possibile usare la funzione @@ERROR dopo un'istruzione di Transact-SQL per rilevare se si è verificato un errore durante l'esecuzione dell'istruzione. Prima dell'introduzione della gestione degli errori TRY/CATCH/THROWTransact-SQL, in certi casi i codici restituiti erano necessari per determinare l'esito positivo o negativo delle stored procedure. Le stored procedure devono sempre indicare un esito negativo con un errore (generato mediante THROW/RAISERROR, se necessario) e non basarsi su un codice restituito per la segnalazione dell'errore. È consigliabile evitare l'uso del codice restituito anche per la restituzione di dati dell'applicazione.

Esempi di codici restituiti

Nell'esempio seguente viene illustrata la procedura usp_GetSalesYTD con una modalità di gestione degli errori che consente di impostare valori speciali del codice restituito per errori diversi. Nella tabella seguente sono inclusi i valori interi assegnati dalla procedura a ogni possibile errore e viene indicato il significato di ogni valore.

Valore del codice restituito	Significato
0	L'esecuzione è stata completata.
1	Non è stato specificato un valore obbligatorio per un parametro.
2	Il valore specificato per il parametro non è valido.
3	Si è verificato un errore durante il recupero del valore delle vendite.
4	È stato trovato un valore delle vendite NULL per il venditore.

L'esempio crea una routine denominata Sales.usp_GetSalesYTD, che:

• Dichiara il parametro @SalesPerson e imposta il valore predefinito su NULL. Questo parametro è destinato ad accettare il cognome di un venditore.
• Convalida il parametro @SalesPerson.
  • Se @SalesPerson è NULL, la procedura stampa un messaggio e restituisce il codice restituito 1.
  • In caso contrario, se il parametro @SalesPerson non è NULL, la routine controlla il numero di righe nella tabella HumanResources.vEmployee con un cognome uguale al valore di @SalesPerson. Se il conteggio è zero, la procedura restituisce il codice restituito 2.
• Esegue una query sulle vendite dall'inizio dell'anno per il venditore con il cognome specificato e lo assegna al parametro di output @SalesYTD.
• Verifica la presenza di errori di SQL Server testando @@ERROR (Transact-SQL).
  • Se @@ERROR non è uguale a zero, la procedura restituisce il codice restituito 3.
  • Se @@ERROR fosse uguale a zero, la procedura verificherebbe se il valore del parametro @SalesYTD è NULL. Se non sono state trovate vendite dall'inizio dell'anno alla data attuale, la procedura restituisce il codice restituito 4.
  • Se nessuna delle condizioni precedenti è true, la procedura restituisce il codice restituito 0.
• Se raggiunta, l'istruzione finale nella stored procedure richiama in modo ricorsivo la stored procedure senza specificare un valore di input.

Alla fine dell'esempio viene fornito il codice per eseguire la routine Sales.usp_GetSalesYTD specificando un cognome per il parametro di input e salvando il valore di output nella variabile @SalesYTD.

USE AdventureWorks2022;  
GO
  
CREATE PROCEDURE Sales.usp_GetSalesYTD 
    @SalesPerson NVARCHAR(50) = NULL, 
    @SalesYTD MONEY=NULL OUTPUT
AS
    IF @SalesPerson IS NULL 
    BEGIN
        PRINT 'ERROR: You must specify a last name for the sales person.'
        RETURN (1)
    END
    ELSE 
    BEGIN
        IF(SELECT COUNT(*)FROM HumanResources.vEmployee WHERE LastName=@SalesPerson)=0
            RETURN (2)
    END

    SELECT @SalesYTD=SalesYTD
    FROM Sales.SalesPerson AS sp
         JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID=sp.BusinessEntityID
    WHERE LastName=@SalesPerson;

    IF @@ERROR<>0 
    BEGIN
        RETURN (3)
    END 
    ELSE 
    BEGIN
        IF @SalesYTD IS NULL 
            RETURN (4)
        ELSE 
            RETURN (0)
    END

    EXEC Sales.usp_GetSalesYTD;
GO


DECLARE @SalesYTDForSalesPerson money, @ret_code int;  

EXECUTE Sales.usp_GetSalesYTD  N'Blythe', @SalesYTD = @SalesYTDForSalesPerson OUTPUT;  

PRINT N'Year-to-date sales for this employee is ' +  
    CONVERT(varchar(10), @SalesYTDForSalesPerson);  
GO

Nell'esempio seguente viene creato un programma per la gestione dei codici restituiti dalla procedura usp_GetSalesYTD .

Esempio:

• Dichiara le variabili @SalesYTDForSalesPerson e @ret_code per ricevere il valore di output e il codice restituito della routine.
• Esegue la procedura Sales.usp_GetSalesYTD con un valore di input specificato per @SalesPerson e salva il valore di output e il codice restituito nelle variabili.
• Controlla il codice restituito in @ret_code e chiama PRINT (Transact-SQL) per visualizzare un messaggio appropriato.

DECLARE @SalesYTDForSalesPerson money, @ret_code int;  
  
EXECUTE @ret_code = Sales.usp_GetSalesYTD  
    N'Blythe', @SalesYTD = @SalesYTDForSalesPerson OUTPUT;  

IF @ret_code = 0  
    BEGIN  
        PRINT 'Procedure executed successfully';
        PRINT 'Year-to-date sales for this employee is ' + CONVERT(varchar(10),@SalesYTDForSalesPerson);
    END  
ELSE IF @ret_code = 1  
   PRINT 'ERROR: You must specify a last name for the sales person.';
ELSE IF @ret_code = 2   
   PRINT 'ERROR: You must enter a valid last name for the sales person.';
ELSE IF @ret_code = 3  
   PRINT 'ERROR: An error occurred getting sales value.';
ELSE IF @ret_code = 4  
   PRINT 'ERROR: No sales recorded for this employee.';
GO

Contenuto correlato

Per altre informazioni sulle stored procedure e sui concetti correlati, vedere gli articoli seguenti:

• CREATE PROCEDURE (Transact-SQL)
• PRINT (Transact-SQL)
• SET @local_variable (Transact-SQL)
• RETURN (Transact-SQL)
• @@ERROR (Transact-SQL)