Tárolt eljárásból származó adatok visszaadása

2025-01-02

A következőkre vonatkozik:SQL Server Azure SQL Database Felügyelt Azure SQL-példány Azure Synapse Analytics Microsoft Fabric-ben lévő SQL-adatbázis

Az eljárásból a hívó programba történő adatvisszaadásnak három módja van: eredményhalmazok, kimeneti paraméterek és visszatérési kódok. Ez a cikk a három megközelítésről nyújt tájékoztatást.

A cikkben szereplő kódminták a AdventureWorks2022 vagy AdventureWorksDW2022 mintaadatbázist használják, amelyet a Microsoft SQL Server-minták és közösségi projektek kezdőlapjáról tölthet le.

Adatok visszaadása eredményhalmazokkal

Ha SELECT utasítást tartalmaz egy tárolt eljárás törzsében (de nem SELECT ... INTO vagy INSERT ... SELECT), a SELECT utasítás által megadott sorokat a rendszer közvetlenül az ügyfélnek küldi el. Nagy eredményhalmazok esetén a tárolt eljárás végrehajtása nem folytatódik a következő utasításig, amíg az eredményhalmazt nem küldi el teljesen az ügyfélnek. Kis eredményhalmazok esetén az eredmények tárolódnak és elküldésre készülnek az ügyfél számára, majd a végrehajtás folytatódik. Ha a tárolt eljárás végrehajtása során több ilyen SELECT utasítás fut, a rendszer több eredményhalmazt küld az ügyfélnek. Ez a viselkedés a beágyazott Transact-SQL kötegekre, a beágyazott tárolt eljárásokra és a legfelső szintű Transact-SQL kötegekre is vonatkozik.

Példák az adatok eredményhalmaz használatával történő visszaadására

Ez a példa egy tárolt eljárást mutat be, amely a LastName nézetben is megjelenő összes SalesYTD sor SalesPerson és vEmployee értékeit adja vissza.

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

Adatok visszaadése kimeneti paraméterrel

Ha egy paraméter kimeneti kulcsszóját adja meg az eljárásdefinícióban, az eljárás visszaadhatja a paraméter aktuális értékét a hívó programnak, amikor az eljárás kilép. A paraméter értékének a hívó programban használható változóban való mentéséhez a hívó programnak a kimeneti kulcsszót kell használnia az eljárás végrehajtásakor. További információ arról, hogy milyen adattípusok használhatók kimeneti paraméterekként, lásd CREATE PROCEDURE.

Példák kimeneti paraméterekre

Az alábbi példa egy bemenettel és egy kimeneti paraméterrel rendelkező eljárást mutat be. A @SalesPerson paraméter a hívó program által megadott bemeneti értéket kapja. A SELECT utasítás a bemeneti paraméternek átadott értéket használja a helyes SalesYTD érték lekéréséhez. A SELECT utasítás az értéket a @SalesYTD kimeneti paraméterhez is hozzárendeli, amely az eljárást elhagyva visszaadja az értéket a hívó programnak.

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

Az alábbi példa meghívja az első példában létrehozott eljárást, és menti a hívott eljárás által visszaadott @SalesYTD kimeneti paramétert a @SalesYTDBySalesPerson változóba.

Példa:

Deklarálja az eljárás kimeneti értékének fogadásához @SalesYTDBySalesPerson változót.
Végrehajtja az Sales.uspGetEmployeeSalesYTD eljárást, amely megadja a bemeneti paraméter családnevét. Mentse a kimeneti értéket a @SalesYTDBySalesPersonváltozóban.
A mentett érték megjelenítéséhez meghívja a @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

Az eljárás végrehajtásakor bemeneti értékek is megadhatóak a kimeneti paraméterekhez. Ez lehetővé teszi, hogy az eljárás értéket fogadjon a hívó programtól, módosítsa vagy végrehajtsa a műveleteket az értékkel, majd visszaadja az új értéket a hívó programnak. Az előző példában a @SalesYTDBySalesPerson változóhoz hozzá lehet rendelni egy értéket, mielőtt a program meghívja a Sales.uspGetEmployeeSalesYTD eljárást. A végrehajtott utasítás átadná a @SalesYTDBySalesPerson változó értékét a @SalesYTD kimeneti paraméternek. Ezután az eljárás törzsében az érték használható az új értéket létrehozó számításokhoz. Az új értéket a kimeneti paraméteren keresztül adja vissza az eljárásból, és frissíti az értéket a @SalesYTDBySalesPerson változóban, amikor az eljárás kilép. Ezt gyakran pass-by-reference képességnek is nevezik.

Ha egy paraméter kimenetét adja meg, amikor meghív egy eljárást, és a paraméter nincs definiálva az eljárásdefiníció kimenetének használatával, hibaüzenet jelenik meg. A kimeneti paraméterekkel rendelkező eljárásokat azonban végrehajthatja, és nem adhat meg kimenetet az eljárás végrehajtásakor. A program nem ad vissza hibát, de a kimeneti érték nem használható a hívó programban.

A kurzor adattípusának használata a kimeneti paraméterekben

Transact-SQL eljárások csak kimeneti paraméterek esetén használhatják a kurzor adattípusát. Ha a kurzor adattípusa egy paraméterhez van megadva, az eljárásdefinícióban meg kell adni a változó és a kimeneti kulcsszavakat is. Egy paraméter csak kimenetként adható meg, de ha a paraméterdeklarációban a változó kulcsszó van megadva, az adattípusnak kurzornak kell lennie, és a kimeneti kulcsszót is meg kell adni.

Jegyzet

A kurzor adattípusa nem köthető alkalmazásváltozókhoz az adatbázis API-jai, például az OLE DB, az ODBC, az ADO és a DB-Library használatával. Mivel a kimeneti paramétereket meg kell kötni, mielőtt egy alkalmazás végrehajthat egy eljárást, a kurzorkimeneti paraméterekkel rendelkező eljárásokat nem lehet meghívni az adatbázis API-jaiból. Ezek az eljárások csak akkor hívhatók meg Transact-SQL kötegekből, eljárásokból vagy eseményindítókból, ha a kurzor kimeneti változója egy Transact-SQL helyi kurzorváltozóhoz van rendelve.

A kurzor kimeneti paramétereinek szabályai

Az alábbi szabályok a kurzor kimeneti paramétereire vonatkoznak az eljárás végrehajtásakor:

Csak előre mutató kurzor esetén a kurzor eredményhalmazában visszaadott sorok csak azok a sorok, amelyek a kurzor pozíciójánál és azon túl vannak az eljárás végrehajtásának befejezésekor. Például:
- A nem görgethető kurzor egy 100 sorból álló RS nevű eredményhalmaz eljárásában nyílik meg.
- Az eljárás lekéri az első öt sort az eredményhalmazból RS.
- Az eljárás visszakerül a hívóhoz.
- A hívónak visszaadott RS eredményhalmaz a RS6–100 sorából áll, és a hívó kurzora a RSelső sora elé kerül.
Csak előre mutató kurzor esetén, ha a kurzor az első sor előtt van az eljárás kilépésekor, a rendszer a teljes eredménykészletet visszaadja a hívási kötegnek, eljárásnak vagy eseményindítónak. Visszatéréskor a kurzor pozíciója az első sor előtt van beállítva.
Előrefelé kurzor esetén, ha a kurzor az utolsó sor után helyezkedik el az eljárás befejezésekor, a rendszer üres eredményhalmazt ad vissza a hívási köteghez, eljáráshoz vagy triggerhez.

Jegyzet

Az üres eredményhalmaz nem azonos a null értékkel.
Görgethető kurzor esetén az eredményhalmaz összes sora visszakerül a hívási kötegbe, az eljárásba vagy az eseményindítóba, amikor az eljárás kilép. Visszatéréskor a kurzor pozíciója az eljárás során végrehajtott utolsó beolvasás helyén marad.
Bármilyen típusú kurzor esetén, ha a kurzor bezárva van, a függvény null értéket ad vissza a hívó kötegnek, eljárásnak vagy eseményindítónak. Ez akkor is így van, ha a kurzor egy paraméterhez van rendelve, de a kurzor soha nem nyílik meg.

Jegyzet

A bezárt állapot csak a visszatérési időpontban számít. Például érvényes a kurzort az eljárás alatt részben bezárni, majd később újra megnyitni, és az újra megnyitott kurzor eredményhalmazát visszaadni a hívási kötegnek, eljárásnak vagy eseményindítónak.

Példák a kurzor kimeneti paramétereire

Az alábbi példában egy olyan eljárás jön létre, amely egy kimeneti paramétert adott meg, @CurrencyCursor a kurzor adattípusával. Ezt követően az eljárást tömegesen hívják meg.

Először hozza létre az eljárást, amely deklarálja és megnyitja a kurzort a Currency táblán.

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

Ezután hajt végre egy köteget, amely deklarál egy helyi kurzorváltozót, végrehajtja a kurzor helyi változóhoz való hozzárendelésének eljárását, majd lekéri a sorokat a kurzorból.

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

Adatok visszaadása visszatérési kóddal

Az eljárás visszaadhat egy egész számot, úgynevezett visszatérési kódot egy eljárás végrehajtási állapotának jelzésére. Egy eljárás visszatérési kódját a RETURNhasználatával adhatja meg. A kimeneti paraméterekhez hasonlóan a visszatérési kódot is egy változóba kell mentenie az eljárás végrehajtásakor a visszatérési kód értékének a hívó programban való használatához. Az @result hozzárendelési változó például a my_proceljárásból származó visszatérési kód tárolására szolgál, például:

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

A visszatérési kódokat gyakran használják a folyamatblokkok vezérlésére az eljárásokban, hogy minden lehetséges hibahelyzethez meg lehessen adni a visszatérési kód értékét. A @@ERROR függvényt egy Transact-SQL utasítás után használhatja annak észlelésére, hogy hiba történt-e az utasítás végrehajtása során. A TRY/CATCH/THROW hibakezelés bevezetése előtt a Transact-SQL a tárolt eljárások sikerességének vagy sikertelenségének meghatározásához szükség volt a visszatérési kódokra. A tárolt eljárásoknak mindig hibát kell jelezniük egy hibaüzenet generálásával (szükség esetén THROW/RAISERROR), és nem szabad, hogy visszatérési kódra támaszkodjanak a hiba jelzéséhez. Ne használja a visszatérési kódot az alkalmazásadatok visszaadásához.

Példák visszatérési kódokra

Az alábbi példa a usp_GetSalesYTD olyan hibakezelési eljárást mutatja be, amely speciális visszatérési kódértékeket állít be a különböző hibákhoz. Az alábbi táblázat az eljárás által az egyes lehetséges hibákhoz rendelt egész számértéket és az egyes értékek megfelelő jelentését mutatja.

Kódérték visszaadása	Jelentés
`0`	Sikeres végrehajtás.
`1`	Nincs megadva a szükséges paraméterérték.
`2`	A megadott paraméterérték érvénytelen.
`3`	Hiba történt az értékesítési érték lekérése közben.
`4`	`NULL` értékesítési érték található az értékesítő számára.

A példa létrehoz egy Sales.usp_GetSalesYTDnevű eljárást, amely:

Deklarálja a @SalesPerson paramétert, és az alapértelmezett értékét NULLértékre állítja. Ez a paraméter egy értékesítési személy családnevét kívánja felvenni.
Ellenőrzi a @SalesPerson paramétert.
- Ha @SalesPersonNULL, az eljárás egy üzenetet nyomtat ki, és visszaadja a visszatérési kódot 1.
- Ellenkező esetben, ha a @SalesPerson paraméter nem NULL, az eljárás ellenőrzi a HumanResources.vEmployee táblában lévő sorok számát, ahol a családnév megegyezik a @SalesPersonértékével. Ha a szám nulla, az eljárás a visszatérési kódot adja vissza 2.
Lekérdezi a megadott családnévvel rendelkező értékesítési személy évről évre történő értékesítését, és hozzárendeli a @SalesYTD kimeneti paraméterhez.
Sql Server-hibák ellenőrzése a @@ERRORtesztelésével.
- Ha @@ERROR nem egyenlő nullával, az eljárás a visszatérési kódot adja vissza 3.
- Ha @@ERROR nullával egyenlő volt, az eljárás ellenőrzi, hogy a @SalesYTD paraméter értéke NULL-e. Ha nem található évről évre szóló értékesítés, az eljárás a 4visszatérési kódot adja vissza.
- Ha az előző feltételek egyike sem igaz, az eljárás a 0visszatérési kódot adja vissza.
Ha elérték, a tárolt eljárás végső utasítása rekurzív módon hívja meg a tárolt eljárást bemeneti érték megadása nélkül.

A példa végén kód jelenik meg a Sales.usp_GetSalesYTD eljárás végrehajtásához, miközben megadja a bemeneti paraméter családnevét, és menti a kimeneti értéket a @SalesYTDváltozóban.

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

Az alábbi példa létrehoz egy programot a usp_GetSalesYTD eljárásból visszaadott visszatérési kódok kezelésére.