Zurückgeben von Daten aus einer gespeicherten Prozedur

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Es gibt drei Methoden, Daten aus einer Prozedur an ein aufrufendes Programm zurückzugeben: Resultsets, Ausgabeparameter und Rückgabecodes. Dieser Artikel enthält Informationen zu diesen drei Ansätzen.

Zurückgeben von Daten mithilfe von Resultsets

Wenn Sie eine SELECT-Anweisung im Textkörper einer gespeicherten Prozedur (jedoch nicht die Anweisungen SELECT... INTO oder INSERT... SELECT) einfügen, werden die Zeilen, die durch die SELECT-Anweisung angegeben werden, direkt an den Client gesendet. Bei großen Resultsets wird die Ausführung der gespeicherten Prozedur erst mit der nächsten Anweisung fortgesetzt, wenn das Resultset vollständig an den Client gesendet wurde. Bei kleinen Resultsets werden die Ergebnisse für die Rückgabe an den Client in einem Spool erstellt, und die Ausführung wird fortgesetzt. Wenn mehrere dieser SELECT-Anweisungen während der Ausführung der gespeicherten Prozedur ausgeführt werden, werden mehrere Resultsets an den Client gesendet. Dieses Verhalten gilt auch für geschachtelte Transact-SQL-Batches, geschachtelte gespeicherte Prozeduren und Transact-SQL-Batches der obersten Ebene.

Beispiele für das Zurückgeben von Daten mithilfe eines Resultsets

In den folgenden Beispielen wird die AdventureWorks2022-Beispieldatenbank verwendet. Dieses Beispiel zeigt eine gespeicherte Prozedur, die die Werte LastName und SalesYTD für alle SalesPerson-Zeilen zurückgibt, die ebenfalls in der Ansicht vEmployee angezeigt werden.

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 

Zurückgeben von Daten mithilfe eines Ausgabeparameters

Wenn Sie in der Prozedurdefinition für einen Parameter das Schlüsselwort „output“ angeben, kann die Prozedur den aktuellen Wert des Parameters an das aufrufende Programm zurückgeben, wenn die Prozedur beendet wird. Um den Wert des Parameters in einer Variablen zu speichern, die in dem aufrufenden Programm verwendet werden kann, muss das aufrufende Programm beim Ausführen der Prozedur das Schlüsselwort „output“ verwenden. Weitere Informationen zu den Datentypen, die als Ausgabeparameter verwendet werden können, finden Sie unter CREATE PROCEDURE (Transact-SQL).

Beispiele für Ausgabeparameter

Das folgende Beispiel zeigt eine Prozedur mit einem Eingabe- und einem Ausgabeparameter. Der @SalesPerson -Parameter würde einen vom aufrufenden Programm angegebenen Eingabewert empfangen. Die SELECT-Anweisung verwendet den im Eingabeparameter übergebenen Wert, um den richtigen SalesYTD -Wert abzurufen. Die SELECT-Anweisung weist den Wert auch dem @SalesYTD -Ausgabeparameter zu, der den Wert an das aufrufende Programm zurückgibt, wenn die Prozedur beendet wird.

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 

Im folgenden Beispiel wird die im ersten Beispiel erstellte Prozedur aufgerufen und der Ausgabeparameter @SalesYTD gespeichert, der von der aufgerufenen Prozedur in der @SalesYTDBySalesPerson-Variablen zurückgegeben wurde.

Beispiel:

• Deklariert die Variable @SalesYTDBySalesPerson, die den Ausgabewert der Prozedur empfangen soll.
• Führt die Prozedur Sales.uspGetEmployeeSalesYTD aus und gibt für den Eingabeparameter einen Nachnamen an. Speichert den Ausgabewert in der Variablen @SalesYTDBySalesPerson.
• Ruft PRINT auf, um den in @SalesYTDBySalesPerson gespeicherten Wert anzuzeigen.

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

Eingabewerte können auch als Ausgabeparameter angegeben werden, wenn die Prozedur ausgeführt wird. Auf diese Weise kann die Prozedur einen Wert von dem aufrufenden Programm erhalten, diesen Wert ändern oder andere Operationen mit dem Wert ausführen und den neuen Wert anschließend an das aufrufende Programm zurückgeben. In dem zuvor aufgeführten Beispiel kann der @SalesYTDBySalesPerson-Variablen ein Wert zugewiesen werden, bevor das Programm die Sales.uspGetEmployeeSalesYTD-Prozedur aufruft. Die EXECUTE-Anweisung übergibt in diesem Fall den @SalesYTDBySalesPerson-Variablenwert an den Ausgabeparameter @SalesYTD. Im Prozedurtext kann der Wert zu Berechnungen verwendet werden, die einen neuen Wert generieren. Der neue Wert wird über den Ausgabeparameter aus der Prozedur zurückgegeben, wobei der Wert in der Variablen @SalesYTDBySalesPerson aktualisiert wird, wenn die Prozedur beendet wird. Dies wird häufig als "Fähigkeit zur Verweisübergabe" bezeichnet.

Wenn beim Aufruf einer Prozedur „output“ für einen Parameter angegeben wird und dieser Parameter in der Prozedurdefinition nicht mithilfe von „output“ definiert worden ist, erhalten Sie eine Fehlermeldung. Sie können jedoch eine Prozedur mit Ausgabeparameter ausführen, ohne „output“ beim Ausführen der Prozedur anzugeben. In diesem Fall wird kein Fehler zurückgegeben; der Ausgabewert kann jedoch nicht im aufrufenden Programm verwendet werden.

Verwenden des Cursordatentyps in Ausgabeparametern

Transact-SQL-Prozeduren können den Datentyp Cursor nur für Ausgabeparameter verwenden. Wenn der Datentyp Cursor für einen Parameter angegeben wird, müssen in der Prozedurdefinition sowohl das Schlüsselwort „varying“ als auch das Schlüsselwort „output“ für diesen Parameter angegeben werden. Für einen Parameter kann nur das Schlüsselwort „output“ angegeben werden. Wenn allerdings das Schlüsselwort „varying“ in der Parameterdeklaration angegeben wurde, muss der Datentyp Cursor lauten und zudem das Schlüsselwort „output“ angegeben werden.

Hinweis

Der Datentyp cursor kann nicht durch Datenbank-APIs, wie z. B. OLE DB, ODBC, ADO und DB-Library, an Anwendungsvariablen gebunden werden. Da Ausgabeparameter gebunden werden müssen, bevor eine Anwendung eine Prozedur ausführen kann, können Prozeduren mit Ausgabeparametern vom Typ Cursor nicht über die Datenbank-APIs aufgerufen werden. Diese Prozeduren können nur dann über Transact-SQL-Batches, -Prozeduren oder -Trigger aufgerufen werden, wenn die Ausgabevariable Cursor einer lokalen Transact-SQL-Variablen vom Typ Cursor zugewiesen ist.

Regeln für Cursorausgabeparameter

Folgende Regeln gelten für Ausgabeparameter vom cursor -Datentyp, wenn die Prozedur ausgeführt wird:

• Bei einem Vorwärtscursor werden im Resultset des Cursors nur die Zeilen zurückgegeben, die sich am Ende der Ausführung der Prozedur an und hinter der Cursorposition befinden. Beispiel:

  • Ein nicht scrollbarer Cursor wird in einer Prozedur für ein Resultset namens RS geöffnet, das 100 Zeilen umfasst.

  • Die Prozedur ruft die ersten 5 Zeilen des Resultsets RS ab.

  • Die Prozedur gibt das Resultset an den Aufrufer zurück.

  • Das Resultset RS, das an den Aufrufer zurückgegeben wird, besteht aus den Zeilen 6 bis 100 von RS, und der Cursor im Aufrufer wird vor der ersten Zeile von RS platziert.

• Wenn ein Vorwärtscursor beim Beenden der Prozedur vor der ersten Zeile positioniert ist, wird das gesamte Resultset an den aufrufenden Batch, die aufrufende Prozedur oder den aufrufenden Trigger zurückgegeben. Nach der Rückgabe wird der Cursor vor der ersten Zeile positioniert.

• Wenn ein Vorwärtscursor beim Beenden der Prozedur hinter dem Ende der letzten Zeile positioniert ist, wird ein leeres Resultset an den aufrufenden Batch, die aufrufende Prozedur oder den aufrufenden Trigger zurückgegeben.

  Hinweis

  Ein leeres Resultset ist nicht das Gleiche wie ein NULL-Wert.

• Bei einem bildlauffähigen Cursor werden beim Beenden der Prozedur alle Zeilen im Resultset an den aufrufenden Batch, die aufrufende gespeicherte Prozedur oder den aufrufenden Trigger zurückgegeben. Nach der Rückgabe behält der Cursor die Position des letzten in der Prozedur ausgeführten Abrufs bei.

• Bei allen Cursortypen wird ein NULL-Wert an den aufrufenden Batch, die aufrufende Prozedur oder den aufrufenden Trigger zurückgegeben, wenn der Cursor geschlossen ist. Dies ist auch dann der Fall, wenn ein Cursor einem Parameter zugewiesen ist, dieser Cursor jedoch nie geöffnet wird.

  Hinweis

  Der geschlossene Status ist nur zum Zeitpunkt der Rückgabe relevant. Beispielsweise ist es zulässig, einen Cursor während eines Teils der Prozedur zu schließen, ihn zu einem späteren Zeitpunkt in der Prozedur wieder zu öffnen und das Resultset dieses Cursors an den aufrufenden Batch, die aufrufende Prozedur oder den aufrufenden Trigger zurückzugeben.

Beispiele für Cursorausgabeparameter

Im folgenden Beispiel wird eine gespeicherte Prozedur mit einem Ausgabeparameter @CurrencyCursor vom Cursor-Datentyp erstellt. Die Prozedur wird anschließend in einem Batch aufgerufen.

Zuerst wird die Prozedur erstellt, die einen Cursor für die Currency-Tabelle deklariert und dann öffnet.

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  

Als Nächstes wird ein Batch ausgeführt, der eine lokale Cursorvariable deklariert, die Prozedur ausführt, um der lokalen Variablen den Cursor zuzuweisen, und dann die Zeilen aus dem Cursor abruft.

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    

Zurückgeben von Daten mithilfe eines Rückgabecodes

Eine Prozedur kann einen ganzzahligen Wert zurückgeben, der als Rückgabecode bezeichnet wird, um den Ausführungsstatus einer Prozedur anzuzeigen. Sie geben den Rückgabecode für eine Prozedur mithilfe der RETURN-Anweisung an. Wie bei Ausgabeparametern müssen Sie auch den Rückgabecode in einer Variablen speichern, wenn die Prozedur ausgeführt wird, damit der Wert des Rückgabecodes im aufrufenden Programm verwendet werden kann. So wird z. B. in den folgenden Codezeilen die Zuweisungsvariable @result vom Datentyp int verwendet, um den Rückgabecode der Prozedur my_proc zu speichern:

DECLARE @result int;

EXECUTE @result = my_proc;
GO

Rückgabecodes werden häufig in Blöcken zur Ablaufsteuerung innerhalb von Prozeduren verwendet, um den Wert des Rückgabecodes für sämtliche Fehler festzulegen. Sie können die @@ERROR-Funktion nach einer Transact-SQL-Anweisung verwenden, um festzustellen, ob während der Ausführung der Anweisung ein Fehler aufgetreten ist. Vor der Einführung der TRY/CATCH/THROW Fehlerbehandlung in Transact-SQL waren Rückgabecodes manchmal erforderlich, um den Erfolg oder Misserfolg von gespeicherten Prozeduren festzustellen. Gespeicherte Prozeduren sollten Misserfolge immer mit einem Fehler (der bei Bedarf durch THROW/RAISERROR generiert wird) anzeigen, und keinen Rückgabecode benötigen, um auf den Fehler hinzuweisen. Ebenfalls sollten Sie vermeiden, den Rückgabecode für das Zurückgeben von Anwendungsdaten zu verwenden.

Beispiele für Rückgabecodes

Das folgende Beispiel zeigt die usp_GetSalesYTD -Prozedur mit Fehlerbehandlung, in der für verschiedene Fehler spezielle Rückgabecodewerte festgelegt sind. In der Tabelle werden die ganzzahligen Werte aufgeführt, die die Prozedur den einzelnen möglichen Fehlern zuweist, sowie die Bedeutung der einzelnen Werte.

Rückgabecodewert	Bedeutung
0	Erfolgreiche Ausführung
1	Der erforderliche Parameterwert ist nicht angegeben.
2	Der angegebene Parameterwert ist ungültig.
3	Beim Abrufen der Vertriebswerte ist ein Fehler aufgetreten.
4	Für diesen Vertriebsmitarbeiter wurde ein Vertriebswert von NULL gefunden.

Im Beispiel wird eine Prozedur namens Sales.usp_GetSalesYTD erstellt, die folgende Schritte durchführt:

• Sie deklariert den Parameter @SalesPerson und legt seinen Standardwert auf NULL fest. Dieser Parameter soll den Nachnamen eines Vertriebsmitarbeiters enthalten.
• Sie überprüft den Parameter @SalesPerson.
  • Wenn @SalesPerson den Wert NULL aufweist, gibt die Prozedur eine Meldung aus und gibt den Rückgabecode 1 zurück.
  • Lautet der Parameter @SalesPerson nicht NULL, überprüft die Prozedur die Anzahl der Zeilen in der Tabelle HumanResources.vEmployee, die einen Nachnamen enthalten, der dem Wert von @SalesPerson entspricht. Wenn die Anzahl gleich null ist, gibt die Prozedur den Rückgabecode 2 zurück.
• Sie fragt den Umsatz seit Jahresbeginn für den Vertriebsmitarbeiter mit dem angegebenen Nachnamen ab und weist ihn dem @SalesYTD-Ausgabeparameter zu.
• Sie prüft auf SQL Server-Fehler durch Testen von @@ERROR (Transact-SQL).
  • Wenn @@ERROR nicht gleich null ist, gibt die Prozedur den Rückgabecode 3 zurück.
  • Wenn @@ERROR gleich null war, überprüft die Prozedur, ob der @SalesYTD-Parameterwert NULL ist. Wenn keine Umsätze seit Jahresbeginn gefunden wurden, gibt die Prozedur den Rückgabecode 4 zurück.
  • Wenn keine der oben genannten Bedingungen erfüllt wird, gibt die Prozedur den Rückgabecode 0 zurück.
• Wenn sie erreicht wird, ruft die letzte Anweisung in der gespeicherten Prozedur die gespeicherte Prozedur rekursiv auf, ohne einen Eingabewert anzugeben.

Am Ende des Beispiels wird Code für die Ausführung der Prozedur Sales.usp_GetSalesYTD bereitgestellt, wobei ein Nachname für den Eingabeparameter angegeben und der Ausgabewert in der Variablen @SalesYTD gespeichert wird.

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

Im folgenden Beispiel wird ein Programm erstellt, das die von der usp_GetSalesYTD -Prozedur zurückgegebenen Rückgabecodes verarbeitet.

Beispiel:

• Deklariert die Variablen @SalesYTDForSalesPerson und @ret_code, um den Ausgabewert und den Rückgabecode der Prozedur zu empfangen.
• Führt die Prozedur Sales.usp_GetSalesYTD mit einem für @SalesPerson angegebenen Eingabewert aus und speichert den Ausgabewert und den Rückgabecode in Variablen.
• Überprüft den Rückgabecode in @ret_code und ruft PRINT (Transact-SQL) auf, um eine entsprechende Meldung anzuzeigen.

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

Zugehöriger Inhalt

Weitere Informationen zu gespeicherten Prozeduren und verwandten Konzepten finden Sie in den folgenden Artikeln:

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