Vrácení dat z uložené procedury

2025-03-05

platí pro:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics sql database v Microsoft Fabric

Existují tři způsoby vrácení dat z procedury do volajícího programu: sady výsledků, výstupní parametry a návratové kódy. Tento článek obsahuje informace o třech přístupech.

Ukázky kódu v tomto článku používají ukázkovou databázi AdventureWorks2022 nebo AdventureWorksDW2022, kterou si můžete stáhnout z domovské stránky ukázky a komunitní projekty Microsoft SQL Serveru.

Vrácení dat pomocí sad výsledků

Pokud do těla uložené procedury zahrnete příkaz SELECT (ale ne SELECT ... INTO nebo INSERT ... SELECT), řádky zadané příkazem SELECT se odešlou přímo klientovi. U velkých sad výsledků nebude provádění uložených procedur pokračovat dalším příkazem, dokud se sada výsledků zcela neodesílala klientovi. U malých sad výsledků jsou výsledky připraveny k navrácení klientovi a pokračuje se v provádění. Pokud se během provádění uložené procedury spustí více takových příkazů SELECT, odešle se klientovi několik sad výsledků. Toto chování platí také pro vnořené Transact-SQL dávky, vnořené uložené procedury a Transact-SQL dávky nejvyšší úrovně.

Příklady vrácení dat pomocí sady výsledků

Tento příklad ukazuje uloženou proceduru, která vrací hodnoty LastName a SalesYTD pro všechny SalesPerson řádky, které se zobrazí také v zobrazení 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
     INNER JOIN HumanResources.vEmployee AS e
         ON e.BusinessEntityID = sp.BusinessEntityID;
RETURN;
GO

Vrácení dat pomocí výstupního parametru

Pokud zadáte výstupní klíčové slovo pro parametr v definici procedury, může procedura vrátit aktuální hodnotu parametru volajícímu programu při ukončení procedury. Chcete-li uložit hodnotu parametru v proměnné, kterou lze použít ve volajícím programu, musí volající program při provádění procedury použít výstupní klíčové slovo. Další informace o tom, jaké datové typy lze použít jako výstupní parametry, naleznete v tématu CREATE PROCEDURE.

Příklady výstupních parametrů

Následující příklad ukazuje proceduru se vstupem a výstupním parametrem. Parametr @SalesPerson obdrží vstupní hodnotu určenou volajícím programem. Příkaz SELECT používá hodnotu předanou vstupnímu parametru k získání správné SalesYTD hodnoty. Příkaz SELECT také přiřadí hodnotu výstupnímu parametru @SalesYTD, který vrátí hodnotu volajícímu programu při ukončení procedury.

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

Následující příklad volá proceduru vytvořenou v prvním příkladu a uloží výstupní parametr @SalesYTD vrácený z volané procedury do proměnné @SalesYTDBySalesPerson.

Příklad:

Deklaruje proměnnou @SalesYTDBySalesPerson pro příjem výstupní hodnoty procedury.
Spustí Sales.uspGetEmployeeSalesYTD proceduru určující název rodiny pro vstupní parametr. Uložte výstupní hodnotu do proměnné @SalesYTDBySalesPerson.
Volání funkce PRINT na adrese zobrazí hodnotu uloženou v @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

Vstupní hodnoty lze také zadat pro výstupní parametry při spuštění procedury. Tento postup umožňuje přijmout hodnotu z volajícího programu, změnit nebo provést operace s hodnotou a pak vrátit novou hodnotu do volajícího programu. V předchozím příkladu může být proměnné @SalesYTDBySalesPerson přiřazena hodnota před tím, než program zavolá proceduru Sales.uspGetEmployeeSalesYTD. Spuštěný příkaz předá hodnotu proměnné @SalesYTDBySalesPerson do výstupního parametru @SalesYTD. V těle procedury by se pak hodnota dala použít pro výpočty, které generují novou hodnotu. Nová hodnota by byla vrácena z procedury prostřednictvím výstupního parametru, čímž by se aktualizovala hodnota v proměnné @SalesYTDBySalesPerson při ukončení procedury. To se často označuje jako předávací schopností.

Pokud při volání procedury zadáte výstup pro parametr a tento parametr není definován pomocí výstupu v definici procedury, zobrazí se chybová zpráva. Můžete však provést proceduru s výstupními parametry a při provádění procedury nezadávat výstup. Nevrátí se žádná chyba, ale ve volajícím programu nemůžete použít výstupní hodnotu.

Použití datového typu kurzoru ve výstupních parametrech

Transact-SQL postupy můžou používat datový typ kurzoru pouze pro výstupní parametry. Pokud je pro parametr zadán datový typ kurzoru, musí být pro tento parametr v definici procedury zadána jak různá, tak výstupní klíčová slova. Parametr lze zadat pouze jako výstup, ale pokud je v deklaraci parametru zadáno různé klíčové slovo, musí být datový typ kurzorem a musí být zadáno také výstupní klíčové slovo.

Poznámka

Datový typ kurzoru nemůže být vázán na proměnné aplikace prostřednictvím databázových rozhraní API, jako jsou OLE DB, ODBC, ADO a DB-Library. Vzhledem k tomu, že výstupní parametry musí být vázány před spuštěním procedury aplikace, procedury s výstupními parametry kurzoru nelze volat z databázových rozhraní API. Tyto postupy lze volat z Transact-SQL dávek, procedur nebo triggerů pouze tehdy, když je výstupní proměnná kurzoru přiřazena k Transact-SQL místní proměnné kurzoru.

Pravidla pro výstupní parametry kurzoru

Následující pravidla se týkají výstupních parametrů kurzoru při spuštění procedury:

V případě kurzoru pouze vpřed jsou řádky vrácené v sadě výsledků kurzoru pouze řádky na pozici kurzoru a nad ní na konci provádění procedury. Například:
- Neprůchodný kurzor je otevřen v proceduře na sadě výsledků nazvané RS s 100 řádky.
- Procedura načte prvních pět řádků sady výsledků RS.
- Procedura se vrátí volajícímu.
- Sada výsledků RS vrácená volajícímu se skládá z řádků od 6 do 100 RSa kurzor v volajícím je umístěn před prvním řádkem RS.
Pro kurzor s pohybem pouze vpřed, pokud je kurzor umístěn před prvním řádkem při ukončení vykonávání procedury, vrátí se celá sada výsledků do volající dávky, procedury nebo triggeru. Po vrácení se pozice kurzoru nastaví před prvním řádkem.
Pro kurzor pouze pro posun vpřed, pokud je kurzor umístěný mimo konec posledního řádku při ukončení procedury, vrátí se prázdná sada výsledků do volající dávky, procedury nebo spouštěče.

Poznámka

Prázdná sada výsledků není stejná jako hodnota null.
U posunovatelného kurzoru se všechny řádky v sadě výsledků vrátí do volající dávky, procedury nebo triggeru, když se procedura ukončí. Po vrácení je pozice kurzoru ponechána na pozici posledního načtení provedeného v postupu.
U jakéhokoli typu kurzoru, pokud je kurzor zavřený, je hodnota null předána zpět volající dávce, proceduře nebo triggeru. Jedná se také o případ, kdy je kurzor přiřazen k parametru, ale tento kurzor se nikdy neotevře.

Poznámka

Uzavřený stav má význam jen při návratu. Je například platné zavřít kurzor v průběhu procedury, otevřít ho znovu později v proceduře a vrátit výsledkovou sadu tohoto kurzoru volající dávce, proceduře nebo triggeru.

Příklady výstupních parametrů kurzoru

V následujícím příkladu se vytvoří procedura, která zadala výstupní parametr, @CurrencyCursor pomocí datového typu kurzoru. Procedura se pak volá hromadně.

Nejprve vytvořte proceduru, která deklaruje a potom otevře kurzor na Currency tabulce.

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

Dále spusťte dávku, která deklaruje místní proměnnou kurzoru, provede proceduru pro přiřazení kurzoru k místní proměnné a potom načte řádky z kurzoru.

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

Vrácení dat pomocí návratového kódu

Procedura může vrátit celočíselnou hodnotu nazvanou návratový kód, který označuje stav spuštění procedury. Návratový kód pro proceduru zadáte pomocí RETURN. Stejně jako u výstupních parametrů je nutné uložit návratový kód do proměnné, když se procedura spustí, aby se v volajícím programu použila návratová hodnota kódu. Například proměnná přiřazení @result datového typu int slouží k uložení návratového kódu z my_procprocedury, například:

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

Návratové kódy se běžně používají v blocích toku řízení v rámci postupů k nastavení návratové hodnoty kódu pro každou možnou chybovou situaci. Funkci @@ERROR můžete použít po příkazu Transact-SQL ke zjištění, jestli během provádění příkazu došlo k chybě. Před zavedením zpracování chyb TRY/CATCH/THROW v Transact-SQL návratových kódů bylo někdy nutné určit úspěch nebo selhání uložených procedur. Uložené procedury by měly vždy indikovat selhání chybou (vygenerovanou s THROW/RAISERROR v případě potřeby) a nespoléhat na návratový kód k označení selhání. Také byste se měli vyhnout použití návratového kódu k vrácení dat aplikace.

Příklady návratových kódů

Následující příklad ukazuje usp_GetSalesYTD proceduru s zpracováním chyb, která nastavuje speciální návratové hodnoty kódu pro různé chyby. Následující tabulka uvádí celočíselnou hodnotu přiřazenou postupem každé možné chybě a odpovídající význam pro každou hodnotu.

Hodnota návratového kódu	Význam
`0`	Úspěšné provedení.
`1`	Požadovaná hodnota parametru není zadána.
`2`	Zadaná hodnota parametru není platná.
`3`	Při získávání hodnoty prodeje došlo k chybě.
`4`	`NULL` prodejní hodnota nalezená pro prodejce.

Příklad vytvoří proceduru s názvem Sales.usp_GetSalesYTD, která:

Deklaruje parametr @SalesPerson a nastaví výchozí hodnotu na NULL. Tento parametr je určený k převzetí jména rodiny prodejce.
Ověří parametr @SalesPerson.
- Pokud je @SalesPersonNULL, procedura vytiskne zprávu a vrátí návratový kód 1.
- V opačném případě, pokud parametr @SalesPerson není NULL, procedura zkontroluje počet řádků v tabulce HumanResources.vEmployee s názvem rodiny, který se rovná hodnotě @SalesPerson. Pokud je počet nula, vrátí procedura návratový kód 2.
Dotazuje prodeje za letošní rok pro prodejce se zadaným příjmením a přiřadí je k výstupnímu parametru @SalesYTD.
Kontroluje chyby SQL Serveru testováním @@ERROR.
- Pokud @@ERROR není rovno nule, vrátí procedura návratový kód 3.
- Pokud @@ERROR byla rovna nule, postup zkontroluje, jestli je hodnota parametru @SalesYTDNULL. Pokud nebyly nalezeny žádné prodeje od začátku roku, procedura vrátí návratový kód 4.
- Pokud není splněna žádná z předchozích podmínek, vrátí procedura návratový kód 0.
Pokud je dosaženo, konečný příkaz v uložené proceduře vyvolá uloženou proceduru rekurzivně bez zadání vstupní hodnoty.

Na konci příkladu se poskytne kód, který spustí proceduru Sales.usp_GetSalesYTD při zadávání názvu rodiny pro vstupní parametr a uložení výstupní hodnoty v proměnné @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

Následující příklad vytvoří program pro zpracování návratových kódů vrácených z usp_GetSalesYTD procedury.