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 diRS
e la posizione del cursore nel chiamante precede la prima riga diRS
.
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
/THROW
Transact-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 suNULL
. 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 restituito1
. - In caso contrario, se il parametro
@SalesPerson
non è NULL, la routine controlla il numero di righe nella tabellaHumanResources.vEmployee
con un cognome uguale al valore di@SalesPerson
. Se il conteggio è zero, la procedura restituisce il codice restituito2
.
- Se
- 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 restituito3
. - 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 restituito4
. - Se nessuna delle condizioni precedenti è true, la procedura restituisce il codice restituito
0
.
- Se
- 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:
Commenti e suggerimenti
https://aka.ms/ContentUserFeedback.
Presto disponibile: Nel corso del 2024 verranno gradualmente disattivati i problemi di GitHub come meccanismo di feedback per il contenuto e ciò verrà sostituito con un nuovo sistema di feedback. Per altre informazioni, vedereInvia e visualizza il feedback per