Gegevens retourneren uit een opgeslagen procedure

Van toepassing op:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Magazijn in Microsoft Fabric SQL-database in Microsoft Fabric

Er zijn drie manieren om gegevens van een procedure naar een aanroepend programma te retourneren: resultatensets, uitvoerparameters en retourcodes. Dit artikel bevat informatie over de drie benaderingen.

De codevoorbeelden in dit artikel gebruiken de AdventureWorks2025, AdventureWorksDW2025, of AdventureWorksLT2025 voorbeelddatabase, die je kunt downloaden van de Azure Data SQL Samples Repository GitHub-repository.

Gegevens retourneren met behulp van resultatensets

Als u een SELECT instructie opneemt in de hoofdtekst van een opgeslagen procedure (maar geen SELECT ... INTO of INSERT ... SELECT), worden de rijen die zijn opgegeven door de SELECT-instructie rechtstreeks naar de client verzonden. Voor grote resultatensets gaat de uitvoering van de opgeslagen procedure pas verder naar de volgende instructie als de resultatenset volledig naar de client is verzonden. Voor kleine resultatensets worden de resultaten gepoold om terug te keren naar de client en de uitvoering wordt voortgezet. Als meerdere dergelijke SELECT-instructies worden uitgevoerd tijdens de uitvoering van de opgeslagen procedure, worden er meerdere resultatensets naar de client verzonden. Dit gedrag is ook van toepassing op geneste Transact-SQL batches, geneste opgeslagen procedures en Transact-SQL batches op topniveau.

Voorbeelden van het retourneren van gegevens met behulp van een resultatenset

In dit voorbeeld ziet u een opgeslagen procedure die de LastName en SalesYTD waarden retourneert voor alle SalesPerson rijen die ook worden weergegeven in de vEmployee weergave.

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

Gegevens retourneren met behulp van een uitvoerparameter

Als u het uitvoerwoord voor een parameter in de proceduredefinitie opgeeft, kan de procedure de huidige waarde van de parameter retourneren aan het aanroepende programma wanneer de procedure wordt afgesloten. Als u de waarde van de parameter wilt opslaan in een variabele die kan worden gebruikt in het aanroepende programma, moet het aanroepende programma het uitvoerwoord gebruiken bij het uitvoeren van de procedure. Zie voor meer informatie over welke gegevenstypen kunnen worden gebruikt als uitvoerparameters CREATE PROCEDURE.

Voorbeelden van uitvoerparameters

In het volgende voorbeeld ziet u een procedure met een invoer en een uitvoerparameter. De parameter @SalesPerson ontvangt een invoerwaarde die is opgegeven door het aanroepende programma. De instructie SELECT gebruikt de waarde die is doorgegeven aan de invoerparameter om de juiste SalesYTD waarde te verkrijgen. De instructie SELECT wijst ook de waarde toe aan de @SalesYTD uitvoerparameter, die de waarde retourneert aan het aanroepende programma wanneer de procedure wordt afgesloten.

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

In het volgende voorbeeld wordt de procedure aangeroepen die in het eerste voorbeeld is gemaakt, en de uitvoerparameter @SalesYTD die door de aangeroepen procedure wordt geretourneerd, wordt opgeslagen in de variabele @SalesYTDBySalesPerson.

Het voorbeeld:

Declareert de variabele @SalesYTDBySalesPerson om de uitvoerwaarde van de procedure te ontvangen.
Hiermee wordt de Sales.uspGetEmployeeSalesYTD procedure uitgevoerd die een familienaam voor de invoerparameter opgeeft. Sla de uitvoerwaarde op in de variabele @SalesYTDBySalesPerson.
Roept PRINT- aan om de waarde weer te geven die is opgeslagen 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

Invoerwaarden kunnen ook worden opgegeven voor uitvoerparameters wanneer de procedure wordt uitgevoerd. Hierdoor kan de procedure een waarde ontvangen van het aanroepende programma, bewerkingen wijzigen of uitvoeren met de waarde en vervolgens de nieuwe waarde retourneren aan het aanroepende programma. In het vorige voorbeeld kan aan de variabele @SalesYTDBySalesPerson een waarde worden toegewezen voordat het programma de Sales.uspGetEmployeeSalesYTD procedure aanroept. De uitgevoerde instructie geeft de waarde van de @SalesYTDBySalesPerson variabele door aan de @SalesYTD uitvoerparameter. Vervolgens kan in de hoofdtekst van de procedure de waarde worden gebruikt voor berekeningen die een nieuwe waarde genereren. De nieuwe waarde wordt doorgegeven uit de procedure via de uitvoerparameter, waarbij de waarde in de @SalesYTDBySalesPerson variabele wordt bijgewerkt wanneer de procedure wordt afgesloten. Dit wordt vaak de mogelijkheid genoemd van pass-by-reference.

Als u uitvoer opgeeft voor een parameter wanneer u een procedure aanroept en die parameter niet wordt gedefinieerd met behulp van uitvoer in de proceduredefinitie, krijgt u een foutbericht. U kunt echter een procedure uitvoeren met uitvoerparameters en geen uitvoer opgeven bij het uitvoeren van de procedure. Er wordt geen foutmelding gegeven, maar u kunt de uitvoer niet gebruiken in het aanroepende programma.

Het gegevenstype cursor gebruiken in uitvoerparameters

Transact-SQL procedures kunnen het gegevenstype cursor alleen gebruiken voor uitvoerparameters. Als het gegevenstype van de cursor is opgegeven voor een parameter, moeten zowel de verschillende als de uitvoertrefwoorden worden opgegeven voor die parameter in de proceduredefinitie. Een parameter kan worden opgegeven als alleen uitvoer, maar als het verschillende trefwoord is opgegeven in de parameterdeclaratie, moet het gegevenstype cursor zijn en moet het uitvoerwoord ook worden opgegeven.

Note

Het gegevenstype cursor kan niet worden gebonden aan toepassingsvariabelen via de database-API's, zoals OLE DB, ODBC, ADO en DB-Library. Omdat uitvoerparameters moeten worden gebonden voordat een toepassing een procedure kan uitvoeren, kunnen procedures met cursoruitvoerparameters niet worden aangeroepen vanuit de database-API's. Deze procedures kunnen alleen worden aangeroepen vanuit Transact-SQL batches, procedures of triggers wanneer de cursoruitvoervariabele is toegewezen aan een Transact-SQL lokale cursorvariabele.

Regels voor cursoruitvoerparameters

De volgende regels hebben betrekking op de uitvoerparameters van de cursor wanneer de procedure wordt uitgevoerd:

Voor een cursor met alleen-doorsturen zijn de rijen die worden geretourneerd in de resultatenset van de cursor alleen de rijen op en buiten de positie van de cursor, aan het einde van de procedureuitvoering. Voorbeeld:
- Een niet-schuifbare cursor wordt geopend in een procedure op een resultatenset met de naam RS van 100 rijen.
- Met de procedure worden de eerste vijf rijen van de resultatenset opgehaald RS.
- De procedure keert terug naar de aanroeper.
- De resultatenset RS geretourneerd aan de aanroeper bestaat uit rijen van 6 tot en met 100 van RSen de cursor in de aanroeper wordt vóór de eerste rij van RSgeplaatst.
Voor een doorstuurcursor, als de cursor vóór de eerste rij is gepositioneerd wanneer de procedure eindigt, wordt de volledige resultaatset geretourneerd aan de aanroepende batch, procedure of trigger. Wanneer de cursor wordt geretourneerd, wordt de positie van de cursor ingesteld vóór de eerste rij.
Voor een alleen-vooruit-cursor, als de cursor zich achter het einde van de laatste rij bevindt wanneer de procedure eindigt, wordt een lege resultatenset geretourneerd naar de aanroepende batch, procedure of trigger.

Note

Een lege resultatenset is niet hetzelfde als een null-waarde.
Voor een schuifbare cursor worden alle rijen in de resultatenset geretourneerd naar de aanroepende batch, procedure of trigger wanneer de procedure wordt afgesloten. Wanneer de cursor wordt geretourneerd, blijft de cursor op de positie van de laatste ophaalopdracht die in de procedure wordt uitgevoerd.
Als de cursor wordt gesloten, wordt voor elk type cursor een null-waarde doorgegeven aan de aanroepende batch, procedure of trigger. Dit is ook het geval als een cursor is toegewezen aan een parameter, maar die cursor nooit wordt geopend.

Note

De gesloten staat is alleen van belang op het moment van terugkeer. Het is bijvoorbeeld toegestaan om een cursor midden in de procedure te sluiten, deze later in de procedure weer te openen en de resultatenset van die cursor terug te geven aan de aanroepende batch, procedure of trigger.

Voorbeelden van cursoruitvoerparameters

In het volgende voorbeeld wordt een procedure gemaakt die een uitvoerparameter heeft opgegeven, @CurrencyCursor met behulp van het gegevenstype cursor. De procedure wordt vervolgens aangeroepen in een batch.

Maak eerst de procedure die declareert en opent vervolgens een cursor op de Currency tabel.

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

Voer vervolgens een batch uit die een lokale cursorvariabele declareert, voert de procedure uit om de cursor toe te wijzen aan de lokale variabele en haalt vervolgens de rijen van de cursor op.

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

Gegevens retourneren met behulp van een retourcode

Een procedure kan een geheel getal retourneren dat een retourcode wordt genoemd om de uitvoeringsstatus van een procedure aan te geven. U geeft de retourcode voor een procedure op met behulp van de RETURN-. Net als bij uitvoerparameters moet u de retourcode opslaan in een variabele wanneer de procedure wordt uitgevoerd om de retourcodewaarde in het aanroepende programma te kunnen gebruiken. De toewijzingsvariabele @result van het gegevenstype int wordt bijvoorbeeld gebruikt om de retourcode op te slaan uit de procedure my_proc, zoals:

DECLARE @result AS INT;
EXECUTE @result = my_proc;
GO

Retourcodes worden vaak gebruikt in controleblokken binnen procedures om de retourcodewaarde in te stellen voor elk mogelijk foutscenario. U kunt de @@ERROR-functie gebruiken na een Transact-SQL instructie om te detecteren of er een fout is opgetreden tijdens de uitvoering van de instructie. Voordat met de introductie van TRY/CATCH/THROW foutafhandeling in Transact-SQL werd begonnen, waren retourcodes soms vereist om het succes of falen van opgeslagen procedures te bepalen. Opgeslagen procedures moeten altijd een fout aangeven met een foutmelding (indien nodig gegenereerd met THROW/RAISERROR) en niet afhankelijk zijn van een retourcode om de fout aan te geven. U moet ook voorkomen dat u de retourcode gebruikt om toepassingsgegevens te retourneren.

Voorbeelden van retourcodes

In het volgende voorbeeld ziet u de usp_GetSalesYTD procedure met foutafhandeling waarmee speciale retourcodewaarden voor verschillende fouten worden ingesteld. In de volgende tabel ziet u de gehele waarde die door de procedure wordt toegewezen aan elke mogelijke fout en de bijbehorende betekenis voor elke waarde.

Waarde van retourcode	Meaning
`0`	Geslaagde uitvoering.
`1`	De vereiste parameterwaarde is niet opgegeven.
`2`	De opgegeven parameterwaarde is niet geldig.
`3`	Er is een fout opgetreden bij het ophalen van de verkoopwaarde.
`4`	`NULL` omzetwaarde gevonden voor de verkoper.

In het voorbeeld wordt een procedure met de naam Sales.usp_GetSalesYTDgemaakt, die:

Declareert de parameter @SalesPerson en stelt de standaardwaarde in op NULL. Deze parameter is bedoeld om de familienaam van een verkoper te gebruiken.
Valideert de parameter @SalesPerson.
- Als @SalesPerson is NULL, drukt de procedure een bericht af en retourneert de retourcode 1.
- Als de parameter @SalesPerson niet is NULL, controleert de procedure het aantal rijen in de HumanResources.vEmployee tabel met een familienaam die gelijk is aan de waarde van @SalesPerson. Als het aantal nul is, retourneert de procedure de retourcode 2.
Hiermee wordt de jaar-tot-heden-verkoop voor de verkoper met de opgegeven familienaam opgevraagd en toegewezen aan de @SalesYTD uitvoerparameter.
Controleert op SQL Server-fouten door @@ERRORte testen.
- Als @@ERROR niet gelijk is aan nul, retourneert de procedure de retourcode 3.
- Als @@ERROR gelijk was aan nul, controleert de procedure of de parameterwaarde @SalesYTD is NULL. Als er geen jaar tot heden verkoop is gevonden, retourneert de procedure de retourcode 4.
- Als geen van de voorgaande voorwaarden waar is, retourneert de procedure de retourcode 0.
Indien bereikt, roept de definitieve instructie in de opgeslagen procedure de opgeslagen procedure recursief aan zonder een invoerwaarde op te geven.

Aan het einde van het voorbeeld wordt code opgegeven om de Sales.usp_GetSalesYTD procedure uit te voeren terwijl u een familienaam opgeeft voor de invoerparameter en de uitvoerwaarde opslaat in de variabele @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

In het volgende voorbeeld wordt een programma gemaakt voor het afhandelen van de retourcodes die worden geretourneerd uit de usp_GetSalesYTD procedure.