Restituire dati da una stored procedureReturn Data from a Stored Procedure

Per il contenuto relativo alle versioni precedenti di SQL Server, vedere Restituire dati da una stored procedure.For content related to previous versions of SQL Server, see Return Data from a Stored Procedure.

Sono disponibili due modalità di restituzione di set di risultati o di dati da una procedura a un programma chiamante, cioè i parametri di output e i codici restituiti.There are two ways of returning result sets or data from a procedure to a calling program: output parameters and return codes. In questo argomento vengono fornite informazioni su entrambi gli approcci.This topic provides information on both approaches.

Restituzione di dati tramite un parametro di outputReturning Data Using an Output Parameter

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.If you specify the OUTPUT keyword for a parameter in the procedure definition, the procedure can return the current value of the parameter to the calling program when the procedure exits. Per salvare il valore del parametro in una variabile che può essere utilizzata nel programma chiamante, in tale programma deve essere utilizzata la parola chiave OUTPUT durante l'esecuzione della procedura.To save the value of the parameter in a variable that can be used in the calling program, the calling program must use the OUTPUT keyword when executing the procedure. Per altre informazioni sui tipi di dati che possono essere usati come parametri di output, vedere CREATE PROCEDURE (Transact-SQL).For more information about what data types can be used as output parameters, see CREATE PROCEDURE (Transact-SQL).

Esempi di parametri di outputExamples of Output Parameter

Nell'esempio seguente viene illustrata una procedura con un parametro di input e uno di output.The following example shows a procedure with an input and an output parameter. Il parametro @SalesPerson riceve un valore di input specificato dal programma chiamante.The @SalesPerson parameter would receive an input value specified by the calling program. L'istruzione SELECT usa il valore passato nel parametro di input per ottenere il valore SalesYTD corretto.The SELECT statement uses the value passed into the input parameter to obtain the correct SalesYTD value. Assegna anche il valore al parametro di output @SalesYTD che restituisce il valore al programma chiamante al termine della procedura.The SELECT statement also assigns the value to the @SalesYTD output parameter, which returns the value to the calling program when the procedure exits.

USE AdventureWorks2012;  
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 valore di output restituito dalla procedura chiamata nella variabile @SalesYTD , che è locale nel programma chiamante.The following example calls the procedure created in the first example and saves the output value returned from the called procedure in the @SalesYTD variable, which is local to the calling program.

-- Declare the variable to receive the output value of the procedure.  
DECLARE @SalesYTDBySalesPerson money;  
-- Execute the procedure specifying a last name for the input parameter  
-- and saving the output value in the variable @SalesYTDBySalesPerson  
EXECUTE Sales.uspGetEmployeeSalesYTD  
    N'Blythe', @SalesYTD = @SalesYTDBySalesPerson OUTPUT;  
-- Display the value returned by the procedure.  
PRINT 'Year-to-date sales for this employee is ' +   
    convert(varchar(10),@SalesYTDBySalesPerson);  
GO  

Inoltre, è possibile specificare valori di input per i parametri OUTPUT quando la procedura viene eseguita.Input values can also be specified for OUTPUT parameters when the procedure is executed. 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.This allows the procedure to receive a value from the calling program, change or perform operations with the value, and then return the new value to the calling program. Nell'esempio precedente, è possibile assegnare un valore alla variabile @SalesYTDBySalesPerson prima che il programma chiami la procedura Sales.uspGetEmployeeSalesYTD .In the previous example, the @SalesYTDBySalesPerson variable can be assigned a value before the program calls the Sales.uspGetEmployeeSalesYTD procedure. L'istruzione di esecuzione passa il valore della variabile @SalesYTDBySalesPerson nel parametro OUTPUT @SalesYTD .The execute statement would pass the @SalesYTDBySalesPerson variable value into the @SalesYTD OUTPUT parameter. Nel corpo della procedura il valore può quindi essere utilizzato per calcoli che consentono di generare un nuovo valore.Then in the procedure body, the value could be used for calculations that generate a new value. Il nuovo valore viene passato di nuovo alla procedura tramite il parametro OUTPUT, aggiornando il valore nella variabile @SalesYTDBySalesPerson al termine della procedura.The new value would be passed back out of the procedure through the OUTPUT parameter, updating the value in the @SalesYTDBySalesPerson variable when the procedure exits. Questa funzionalità viene in genere denominata "funzionalità di passaggio per riferimento".This is often referred to as "pass-by-reference capability."

Se si specifica OUTPUT per un parametro quando si chiama una procedura e tale parametro non è stato definito utilizzando OUTPUT nella definizione della procedura, viene restituito un messaggio di errore.If you specify OUTPUT for a parameter when you call a procedure and that parameter is not defined by using OUTPUT in the procedure definition, you get an error message. Tuttavia, è possibile eseguire una procedura con i parametri di output e non specificare OUTPUT in fase di esecuzione della procedura.However, you can execute a procedure with output parameters and not specify OUTPUT when executing the procedure. Non viene restituito alcun errore, ma non è possibile utilizzare il valore di output nel programma chiamante.No error is returned, but you cannot use the output value in the calling program.

Utilizzo del tipo di dati cursor nei parametri OUTPUTUsing the Cursor Data Type in OUTPUT Parameters

Transact-SQLTransact-SQL consente di usare nelle procedure il tipo di dati cursor solo per i parametri OUTPUT. procedures can use the cursor data type only for OUTPUT parameters. Se per un parametro viene specificato il tipo di dati cursor , nella definizione della procedura devono essere specificate per tale parametro entrambe le parole chiave VARYING e OUTPUT.If the cursor data type is specified for a parameter, both the VARYING and OUTPUT keywords must be specified for that parameter in the procedure definition. 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 e deve essere specificata anche la parola chiave OUTPUT.A parameter can be specified as only OUTPUT but if the VARYING keyword is specified in the parameter declaration, the data type must be cursor and the OUTPUT keyword must also be specified.

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.The cursor data type cannot be bound to application variables through the database APIs such as OLE DB, ODBC, ADO, and DB-Library. Poiché in un'applicazione è possibile eseguire una procedura solo in seguito all'associazione dei parametri OUTPUT, le procedure con parametri OUTPUT di tipo cursor non possono essere chiamate dalle API di database.Because OUTPUT parameters must be bound before an application can execute a procedure, procedures with cursor OUTPUT parameters cannot be called from the database APIs. È possibile chiamare queste procedure da trigger, procedure o batch Transact-SQLTransact-SQL solo quando la variabile OUTPUT di tipo cursor è assegnata a una variabile locale Transact-SQLTransact-SQL di tipo cursor .These procedures can be called from Transact-SQLTransact-SQL batches, procedures, or triggers only when the cursor OUTPUT variable is assigned to a Transact-SQLTransact-SQL local cursor variable.

Regole per parametri di output di tipo cursorRules for Cursor Output Parameters

Quando si esegue la procedura, ai parametri di output di tipo cursor si applicano le regole seguenti:The following rules pertain to cursor output parameters when the procedure is executed:

  • 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:For a forward-only cursor, the rows returned in the cursor's result set are only those rows at and beyond the position of the cursor at the conclusion of the procedure execution, for example:

    • Un cursore non scorrevole viene aperto in una procedura in un set di risultati di 100 righe denominato RS.A nonscrollable cursor is opened in a procedure on a result set named RS of 100 rows.

    • La procedura recupera le prime 5 righe del set di risultati RS.The procedure fetches the first 5 rows of result set RS.

    • La procedura restituisce il set di risultati al chiamante.The procedure returns to its caller.

    • 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.The result set RS returned to the caller consists of rows from 6 through 100 of RS, and the cursor in the caller is positioned before the first row of 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.For a forward-only cursor, if the cursor is positioned before the first row when the procedure exits, the entire result set is returned to the calling batch, procedure, or trigger. Quando viene restituito, il cursore è posizionato prima della prima riga.When returned, the cursor position is set before the first row.

  • 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.For a forward-only cursor, if the cursor is positioned beyond the end of the last row when the procedure exits, an empty result set is returned to the calling batch, procedure, or trigger.

    Nota

    Un set di risultati vuoto non equivale a un valore Null.An empty result set is not the same as a null value.

  • Con cursori scorrevoli, quando è disponibile la procedura, tutte le righe del set di risultati vengono restituite al trigger, alla procedura o al batch chiamante.For a scrollable cursor, all the rows in the result set are returned to the calling batch, procedure, or trigger when the procedure exits. Quando viene restituito, il cursore è nella stessa posizione in cui si trovava durante l'ultima operazione di recupero eseguita dalla procedura.When returned, the cursor position is left at the position of the last fetch executed in the procedure.

  • Con qualsiasi tipo di cursore, se il cursore è chiuso, viene restituito un valore Null al trigger, alla procedura o al batch chiamante.For any type of cursor, if the cursor is closed, then a null value is passed back to the calling batch, procedure, or trigger. Questa situazione si verifica anche quando un cursore viene assegnato a un parametro, ma non viene mai aperto.This will also be the case if a cursor is assigned to a parameter, but that cursor is never opened.

    Nota

    Lo stato chiuso di un cursore è rilevante solo in fase di restituzione.The closed state matters only at return time. È 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.For example, it is valid to close a cursor part of the way through the procedure, to open it again later in the procedure, and return that cursor's result set to the calling batch, procedure, or trigger.

Esempi di parametri di output di tipo cursorExamples of Cursor Output Parameters

Nell'esempio seguente viene creata una procedura in cui è specificato un parametro di output @currencycursor usando il tipo di dati cursor .In the following example, a procedure is created that specified an output parameter, @currencycursor using the cursor data type. La procedura viene quindi chiamata in un batch.The procedure is then called in a batch.

Creare innanzitutto la procedura per la dichiarazione e l'apertura di un cursore per la tabella Currency.First, create the procedure that declares and then opens a cursor on the Currency table.

USE AdventureWorks2012;  
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.Next, execute a batch that declares a local cursor variable, executes the procedure to assign the cursor to the local variable, and then fetches the rows from the cursor.

USE AdventureWorks2012;  
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 restituitoReturning Data Using a Return Code

Una procedura può restituire un valore intero, denominato codice restituito, per indicare lo stato di esecuzione di una procedura.A procedure can return an integer value called a return code to indicate the execution status of a procedure. Per specificare il codice restituito per una procedura, utilizzare l'istruzione RETURN.You specify the return code for a procedure using the RETURN statement. Come per i parametri OUTPUT, è necessario salvare il codice restituito in una variabile quando la procedura viene eseguita per utilizzare il valore del codice restituito nel programma chiamante.As with OUTPUT parameters, you must save the return code in a variable when the procedure is executed in order to use the return code value in the calling program. La variabile di assegnazione @result del tipo di dati int viene ad esempio usata per archiviare il codice restituito dalla procedura my_proc:For example, the assignment variable @result of data type int is used to store the return code from the procedure my_proc, such as:

DECLARE @result int;  
EXECUTE @result = my_proc;  

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.Return codes are commonly used in control-of-flow blocks within procedures to set the return code value for each possible error situation. È possibile usare la funzione @@ERROR dopo un'istruzione Transact-SQLTransact-SQL per rilevare se si è verificato un errore durante l'esecuzione dell'istruzione.You can use the @@ERROR function after a Transact-SQLTransact-SQL statement to detect whether an error occurred during the execution of the statement.

Esempi di codici restituitiExamples of Return Codes

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.The following example shows the usp_GetSalesYTD procedure with error handling that sets special return code values for various errors. Nella tabella seguente sono inclusi i valori interi assegnati dalla procedura a ogni possibile errore e viene indicato il significato di ogni valore.The following table shows the integer value that is assigned by the procedure to each possible error, and the corresponding meaning for each value.

Valore del codice restituitoReturn code value SignificatoMeaning
00 L'esecuzione è stata completata.Successful execution.
11 Non è stato specificato un valore obbligatorio per un parametro.Required parameter value is not specified.
22 Il valore specificato per il parametro non è valido.Specified parameter value is not valid.
33 Si è verificato un errore durante il recupero del valore delle vendite.Error has occurred getting sales value.
44 È stato trovato un valore delle vendite NULL per il venditore.NULL sales value found for the salesperson.
USE AdventureWorks2012;  
GO  
IF OBJECT_ID('Sales.usp_GetSalesYTD', 'P') IS NOT NULL  
    DROP PROCEDURE Sales.usp_GetSalesYTD;  
GO  
CREATE PROCEDURE Sales.usp_GetSalesYTD  
@SalesPerson nvarchar(50) = NULL,  -- NULL default value  
@SalesYTD money = NULL OUTPUT  
AS    

-- Validate the @SalesPerson parameter.  
IF @SalesPerson IS NULL  
   BEGIN  
       PRINT 'ERROR: You must specify a last name for the sales person.'  
       RETURN(1)  
   END  
ELSE  
   BEGIN  
   -- Make sure the value is valid.  
   IF (SELECT COUNT(*) FROM HumanResources.vEmployee  
          WHERE LastName = @SalesPerson) = 0  
      RETURN(2)  
   END  
-- Get the sales for the specified name and   
-- assign it to the output parameter.  
SELECT @SalesYTD = SalesYTD   
FROM Sales.SalesPerson AS sp  
JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID  
WHERE LastName = @SalesPerson;  
-- Check for SQL Server errors.  
IF @@ERROR <> 0   
   BEGIN  
      RETURN(3)  
   END  
ELSE  
   BEGIN  
   -- Check to see if the ytd_sales value is NULL.  
     IF @SalesYTD IS NULL  
       RETURN(4)   
     ELSE  
      -- SUCCESS!!  
        RETURN(0)  
   END  
-- Run the stored procedure without specifying an input value.  
EXEC Sales.usp_GetSalesYTD;  
GO  
-- Run the stored procedure with an input value.  
DECLARE @SalesYTDForSalesPerson money, @ret_code int;  
-- Execute the procedure specifying a last name for the input parameter  
-- and saving the output value in the variable @SalesYTD  
EXECUTE Sales.usp_GetSalesYTD  
    N'Blythe', @SalesYTD = @SalesYTDForSalesPerson OUTPUT;  
PRINT N'Year-to-date sales for this employee is ' +  
    CONVERT(varchar(10), @SalesYTDForSalesPerson);  

Nell'esempio seguente viene creato un programma per la gestione dei codici restituiti dalla procedura usp_GetSalesYTD .The following example creates a program to handle the return codes that are returned from the usp_GetSalesYTD procedure.

-- Declare the variables to receive the output value and return code   
-- of the procedure.  
DECLARE @SalesYTDForSalesPerson money, @ret_code int;  

-- Execute the procedure with a title_id value  
-- and save the output value and return code in variables.  
EXECUTE @ret_code = Sales.usp_GetSalesYTD  
    N'Blythe', @SalesYTD = @SalesYTDForSalesPerson OUTPUT;  
--  Check the return codes.  
IF @ret_code = 0  
BEGIN  
   PRINT 'Procedure executed successfully'  
   -- Display the value returned by the procedure.  
   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 'EERROR: 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  

Vedere ancheSee Also

DECLARE @local_variable (Transact-SQL) DECLARE @local_variable (Transact-SQL)
PRINT (Transact-SQL) PRINT (Transact-SQL)
SET @local_variable (Transact-SQL) SET @local_variable (Transact-SQL)
Cursori Cursors
RETURN (Transact-SQL) RETURN (Transact-SQL)
@@ERROR (Transact-SQL)@@ERROR (Transact-SQL)