Restituire dati da una stored procedure

2025-03-05

Si applica a:SQL Server Database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Database SQL in Microsoft Fabric

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.

Gli esempi di codice in questo articolo usano il database di esempio AdventureWorks2022 o AdventureWorksDW2022, che è possibile scaricare dalla home page Microsoft SQL Server Samples and Community Projects.

Restituire dati tramite i set di risultati

Se si include un'istruzione SELECT nel corpo di una procedura memorizzata (ma non un SELECT ... INTO o INSERT ... SELECT), le righe specificate dall'istruzione SELECT vengono 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 i set di risultati di piccole dimensioni, i risultati vengono memorizzati temporaneamente per essere restituiti al client e l'esecuzione continua. Se più istruzioni SELECT vengono eseguite durante l'esecuzione della stored procedure, al client vengono inviati più set di risultati. Questo comportamento si applica anche ai batch Transact-SQL nidificati, alle stored procedure oppure ai batch Transact-SQL di primo livello.

Esempi di restituzione di dati tramite un set di risultati

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
     INNER 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, la procedura può restituire il valore corrente del parametro al programma chiamante al momento della sua uscita. 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 è possibile usare come parametri di output, vedere CREATE PROCEDURE.

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. L'istruzione SELECT assegna anche il valore al parametro di output @SalesYTD, che restituisce il valore al programma chiamante quando la routine viene chiusa.

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
     INNER 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 procedura di Sales.uspGetEmployeeSalesYTD specificando un nome di famiglia per il parametro di input. Salvare il valore di output nella variabile @SalesYTDBySalesPerson.
Chiama PRINT per visualizzare il valore salvato in @SalesYTDBySalesPerson.

DECLARE @SalesYTDBySalesPerson AS 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 eseguita passerà 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 verrebbe restituito dalla procedura tramite il parametro di output, aggiornando il valore nella variabile @SalesYTDBySalesPerson al termine della procedura. Questa viene spesso definita capacità 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.

Utilizza il 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 del cursore non può essere associato alle variabili dell'applicazione tramite le API di database, ad esempio OLE DB, ODBC, ADO e DB-Library. Poiché i parametri di output devono essere associati prima che un'applicazione possa eseguire una routine, le procedure con parametri di output del cursore non possono essere chiamate dalle API di database. Queste procedure possono essere chiamate da batch, procedure o trigger di Transact-SQL solo quando la variabile output di tipo cursore è assegnata a una variabile locale di Transact-SQL di tipo cursore.

Regole per i parametri di output di tipo cursore

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

Per un cursore forward-only, le righe restituite nel set di risultati del cursore sono solo quelle corrispondenti alla posizione del cursore e oltre, al termine dell'esecuzione della procedura. Per esempio:
- Un cursore non scorrevole viene aperto in una procedura su un set di risultati di 100 righe chiamato 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 un cursore forward-only, se il cursore è posizionato prima della prima riga quando la procedura termina, l'intero set di risultati viene restituito al batch, alla procedura o al trigger 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 termina, viene restituito un set di risultati vuoto al batch, alla procedura o al trigger chiamante.

Nota

Un set di risultati vuoto non è uguale a un valore Null.
Per un cursore scorrevole, tutte le righe del set di risultati vengono restituite al batch, alla procedura o al trigger chiamante quando il procedimento termina. Quando viene restituito, il cursore è nella stessa posizione in cui si trovava durante l'ultima operazione di recupero eseguita dalla procedura.
Per qualsiasi tipo di cursore, se il cursore è chiuso, viene restituito un valore null al batch, alla procedura o al trigger chiamante. Questo è anche il caso se un cursore viene assegnato a un parametro, ma tale cursore non viene mai aperto.

Nota

Lo stato chiuso è rilevante solo al momento del ritorno. Ad esempio, è possibile chiudere una parte del cursore durante la procedura, aprirla di nuovo più avanti nella routine e restituire il set di risultati del cursore al batch, alla routine o al trigger chiamante.

Esempi di parametri di output 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 AS CURSOR;

EXECUTE dbo.uspCurrencyCursor
    @CurrencyCursor = @MyCursor OUTPUT;

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

CLOSE @MyCursor;

DEALLOCATE @MyCursor;
GO

Restituire dati utilizzando un codice di ritorno

Una procedura può restituire un valore intero, denominato codice restituito, per indicare lo stato di esecuzione di una procedura. Si specifica il codice di ritorno per una procedura utilizzando il 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. Ad esempio, la variabile di assegnazione @result di tipo di dati int viene usata per archiviare il codice restituito dalla routine my_proc, ad esempio:

DECLARE @result AS 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 in Transact-SQL TRY/CATCH/THROW, a volte era necessario fare affidamento sui codici restituiti per determinare l'esito positivo o negativo delle stored procedure. Le procedure memorizzate devono sempre indicare un errore (generato mediante THROW/RAISERROR, se necessario) e non basarsi su un codice di ritorno per indicare un 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`	Il valore del parametro obbligatorio non è specificato.
`2`	Il valore del parametro specificato non è valido.
`3`	Si è verificato un errore durante il recupero del valore delle vendite.
`4`	`NULL` valore di vendita trovato 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 a ricevere il cognome di un venditore.
Convalida il parametro @SalesPerson.
- Se @SalesPerson è NULL, la routine 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 nome di famiglia uguale al valore di @SalesPerson. Se il conteggio è zero, la procedura restituisce il codice restituito 2.
Esegue una query sulle vendite da inizio anno per il venditore con il cognome specificato e assegna il risultato al parametro di output @SalesYTD.
Verifica la presenza di errori di SQL Server testando @@ERROR.
- Se @@ERROR non è uguale a zero, la procedura restituisce il codice restituito 3.
- Se @@ERROR è uguale a zero, la procedura verifica 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 è vera, la procedura restituisce il codice di ritorno 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 nome di famiglia 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
     INNER 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
EXECUTE Sales.usp_GetSalesYTD;
GO

DECLARE @SalesYTDForSalesPerson AS MONEY, @ret_code AS 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 .