Returnera data från en lagrad procedur

2025-01-02

gäller för:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL-databas i Microsoft Fabric

Det finns tre sätt att returnera data från en procedur till ett anropande program: resultatuppsättningar, utdataparametrar och returkoder. Den här artikeln innehåller information om de tre metoderna.

Kodexemplen i den här artikeln använder AdventureWorks2022- eller AdventureWorksDW2022-exempeldatabasen, som du kan ladda ned från startsidan Microsoft SQL Server Samples och Community Projects.

Returnera data med hjälp av resultatuppsättningar

Om du inkluderar en SELECT-instruktion i brödtexten i en lagrad procedur (men inte en SELECT ... INTO eller INSERT ... SELECT) skickas raderna som anges av SELECT-instruktionen direkt till klienten. För stora resultatuppsättningar fortsätter körningen av den lagrade proceduren inte till nästa instruktion förrän resultatuppsättningen har skickats helt till klienten. För små resultatuppsättningar buffras resultaten för att skickas tillbaka till klienten och körningen fortsätter. Om flera sådana SELECT-instruktioner körs under körningen av den lagrade proceduren skickas flera resultatuppsättningar till klienten. Det här beteendet gäller även kapslade Transact-SQL batchar, kapslade lagrade procedurer och Transact-SQL batchar på toppnivå.

Exempel på hur du returnerar data med hjälp av en resultatuppsättning

Det här exemplet visar en lagrad procedur som returnerar värdena för LastName och SalesYTD för alla SalesPerson rader som också visas i vyn 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

Returnera data med hjälp av en utdataparameter

Om du anger nyckelordet för utdata för en parameter i procedurdefinitionen kan proceduren returnera det aktuella värdet för parametern till det anropande programmet när proceduren avslutas. För att spara värdet för parametern i en variabel som kan användas i det anropande programmet måste det anropande programmet använda nyckelordet utdata när proceduren körs. Mer information om vilka datatyper som kan användas som utdataparametrar finns i CREATE PROCEDURE.

Exempel på utdataparametrar

I följande exempel visas en procedur med indata och en utdataparameter. Parametern @SalesPerson skulle få ett indatavärde som anges av det anropande programmet. SELECT-instruktionen använder värdet som skickas till indataparametern för att hämta rätt SalesYTD värde. Instruktionen SELECT tilldelar också värdet till @SalesYTD-utdataparametern, som returnerar värdet till det anropande programmet när proceduren avslutas.

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

I följande exempel anropas proceduren som skapades i det första exemplet och sparar utdataparametern @SalesYTD som returneras från den anropade proceduren i variabeln @SalesYTDBySalesPerson.

Exemplet:

Deklarerar variabeln @SalesYTDBySalesPerson för att ta emot utdatavärdet för proceduren.
Kör den Sales.uspGetEmployeeSalesYTD proceduren som anger ett familjenamn för indataparametern. Spara utdatavärdet i variabeln @SalesYTDBySalesPerson.
Anropar PRINT- för att visa värdet som sparats i @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

Indatavärden kan också anges för utdataparametrar när proceduren körs. Detta gör att proceduren kan ta emot ett värde från det anropande programmet, ändra eller utföra åtgärder med värdet och sedan returnera det nya värdet till det anropande programmet. I föregående exempel kan variabeln @SalesYTDBySalesPerson tilldelas ett värde innan programmet anropar Sales.uspGetEmployeeSalesYTD proceduren. Den körda instruktionen skickar @SalesYTDBySalesPerson variabelvärdet till @SalesYTD-utdataparametern. I procedurtexten kan värdet sedan användas för beräkningar som genererar ett nytt värde. Det nya värdet skickas tillbaka från proceduren via utdataparametern och uppdaterar värdet i variabeln @SalesYTDBySalesPerson när proceduren avslutas. Detta kallas ofta direktreferens funktion.

Om du anger utdata för en parameter när du anropar en procedur och den parametern inte definieras med hjälp av utdata i procedurdefinitionen får du ett felmeddelande. Du kan dock köra en procedur med utdataparametrar och inte ange utdata när du kör proceduren. Inget fel returneras, men du kan inte använda utdatavärdet i det anropande programmet.

Använda markörens datatyp i utdataparametrar

Transact-SQL procedurer kan endast använda markördatatypen för utdataparametrar. Om markörens datatyp anges för en parameter måste både de varierande nyckelorden och utdatanyckelorden anges för parametern i procedurdefinitionen. En parameter kan anges som endast utdata, men om det varierande nyckelordet anges i parameterdeklarationen måste datatypen vara markör och nyckelordet för utdata måste också anges.

Not

Markörens datatyp kan inte bindas till programvariabler via databas-API:er som OLE DB, ODBC, ADO och DB-Library. Eftersom utdataparametrar måste bindas innan ett program kan köra en procedur kan procedurer med markörutdataparametrar inte anropas från databas-API:erna. Dessa procedurer kan anropas från Transact-SQL batchar, procedurer eller utlösare endast när markörens utdatavariabel tilldelas till en Transact-SQL lokal markörvariabel.

Regler för markörutdataparametrar

Följande regler gäller för markörens utdataparametrar när proceduren körs:

För en framåtriktad markör är de rader som returneras i markörens resultatuppsättning endast de rader som ligger vid och utanför markörens position när proceduren körs. Till exempel:
- En icke-rullningsbar markör öppnas i en procedur på en resultatuppsättning med 100 rader namngiven RS.
- Proceduren hämtar de första fem raderna i resultatuppsättningen RS.
- Proceduren återgår till anroparen.
- Resultatuppsättningen RS som returneras till anroparen består av rader från 6 till 100 av RSoch markören i anroparen placeras före den första raden i RS.
För en framåtriktad markör returneras hela resultatuppsättningen till den anropande batchen, proceduren eller utlösaren om markören placeras före den första raden när proceduren avslutas. När den returneras anges markörens position före den första raden.
För en framåtriktad markör returneras en tom resultatuppsättning till den anropande batchen, proceduren eller utlösaren om markören placeras utanför slutet av den sista raden när proceduren avslutas.

Not

En tom resultatuppsättning är inte samma som ett null-värde.
För en rullningsbar markör returneras alla rader i resultatuppsättningen till den anropande batchen, proceduren eller utlösaren när proceduren avslutas. När den returneras lämnas markörens position vid positionen för den senaste hämtningen som kördes i proceduren.
Om markören stängs för valfri typ av markör skickas ett null-värde tillbaka till den anropande batchen, proceduren eller utlösaren. Detta gäller även om en markör har tilldelats till en parameter, men markören aldrig öppnas.

Anteckning

Det stängda tillståndet är endast viktigt vid returtid. Det är till exempel giltigt att delvis stänga en markör genom proceduren, öppna den igen senare i proceduren och returnera markörens resultatuppsättning till den anropande batchen, proceduren eller utlösaren.

Exempel på parametrar för markörutdata

I följande exempel skapas en procedur som angav en utdataparameter, @CurrencyCursor med hjälp av markördatatypen. Proceduren anropas sedan i en sats.

Skapa först proceduren som deklarerar och öppnar sedan en markör i tabellen Currency.

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

Kör sedan en batch som deklarerar en lokal markörvariabel, kör proceduren för att tilldela markören till den lokala variabeln och hämtar sedan raderna från markören.

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

Returnera data med hjälp av en returkod

En procedur kan returnera ett heltalsvärde som kallas returkod för att ange körningsstatus för en procedur. Du anger returkoden för en procedur med hjälp av RETURN. Precis som med utdataparametrar måste du spara returkoden i en variabel när proceduren körs för att kunna använda returkodvärdet i det anropande programmet. Till exempel används tilldelningsvariabeln @result av datatypen int för att lagra returkoden från proceduren my_proc, till exempel:

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

Returkoder används ofta i kontroll av flödesblock i procedurer för att ange returkodvärdet för varje möjlig felsituation. Du kan använda funktionen @@ERROR efter en Transact-SQL-instruktion för att upptäcka om ett fel uppstod under körningen av Transact-SQL-instruktionen. Före införandet av TRY/CATCH/THROW felhantering i Transact-SQL returkoder krävdes ibland för att fastställa om lagrade procedurer lyckades eller misslyckades. Lagrade procedurer bör alltid indikera fel med ett fel (genereras med THROW/RAISERROR om det behövs) och inte förlita sig på en returkod för att indikera felet. Du bör också undvika att använda returkoden för att returnera programdata.

Exempel på returkoder

I följande exempel visas den usp_GetSalesYTD proceduren med felhantering som anger särskilda returkodsvärden för olika fel. I följande tabell visas det heltalsvärde som tilldelas varje möjligt fel av proceduren och motsvarande betydelse för varje värde.

Återge ett kodvärde	Betydelse
`0`	Lyckat utförande.
`1`	Det obligatoriska parametervärdet har inte angetts.
`2`	Det angivna parametervärdet är inte giltigt.
`3`	Ett fel uppstod när försäljningsvärdet skulle hämtas.
`4`	`NULL` försäljningsvärde har hittats för säljaren.

I exemplet skapas en procedur med namnet Sales.usp_GetSalesYTD, som:

Deklarerar parametern @SalesPerson och anger dess standardvärde till NULL. Den här parametern är avsedd att ta efternamnet på en säljare.
Bekräftar parametern @SalesPerson.
- Om @SalesPerson är NULLskriver proceduren ut ett meddelande och returnerar returkoden 1.
- Om parametern @SalesPerson inte är NULLkontrollerar proceduren annars antalet rader i tabellen HumanResources.vEmployee med ett familjenamn som är lika med värdet för @SalesPerson. Om antalet är noll returnerar proceduren returkoden 2.
Frågar om försäljningen hittills i år för den försäljningsperson med det angivna familjenamnet och tilldelar den till utdata-parametern @SalesYTD.
Söker efter SQL Server-fel genom att testa @@ERROR.
- Om @@ERROR inte är lika med noll returnerar proceduren returkoden 3.
- Om @@ERROR var lika med noll kontrollerar proceduren om parametervärdet @SalesYTD är NULL. Om ingen försäljning hittills i år hittades returnerar proceduren returkoden 4.
- Om inget av ovanstående villkor är sant returnerar proceduren returkoden 0.
Om den slutliga instruktionen i den lagrade proceduren nås anropas den lagrade proceduren rekursivt utan att ange ett indatavärde.

I slutet av exemplet tillhandahålls kod för att köra Sales.usp_GetSalesYTD-proceduren samtidigt som du anger ett familjenamn för indataparametern och sparar utdatavärdet i variabeln @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

I följande exempel skapas ett program för att hantera returkoderna som returneras från usp_GetSalesYTD-proceduren.