Ange parametrar i en lagrad procedur

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-databas i Microsoft Fabric

Genom att ange procedurparametrar kan anropande program skicka värden till procedurens brödtext. Dessa värden kan användas för en mängd olika syften under procedurkörningen. Procedurparametrar kan också returnera värden till det anropande programmet om parametern är markerad som en OUTPUT-parameter.

En procedur kan ha högst 2 100 parametrar. varje tilldelad namn, datatyp och riktning. Du kan också tilldela parametrar standardvärden.

Följande avsnitt innehåller information om hur du skickar värden till parametrar och om hur vart och ett av parameterattributen används under ett proceduranrop.

Observera

Se AdventureWorks serie med exempeldatabaser för den här artikelns övningar. Mer information finns i AdventureWorks-exempeldatabaser.

Skicka värden till parametrar

Parametervärdena som anges med ett proceduranrop måste vara konstanter eller en variabel. Ett funktionsnamn kan inte användas som ett parametervärde. Variabler kan vara användardefinierade eller systemvariabler som @@spid.

I följande exempel visas hur du skickar parametervärden till proceduren uspGetWhereUsedProductID. De visar hur du skickar parametrar som konstanter och variabler och även hur du använder en variabel för att skicka värdet för en funktion.

USE AdventureWorks2022;  
GO  
-- Passing values as constants.  
EXEC dbo.uspGetWhereUsedProductID 819, '20050225';  
GO  
-- Passing values as variables.  
DECLARE @ProductID int, @CheckDate datetime;  
SET @ProductID = 819;  
SET @CheckDate = '20050225';  
EXEC dbo.uspGetWhereUsedProductID @ProductID, @CheckDate;  
GO

I följande exempel returneras ett fel eftersom en funktion inte kan skickas som ett parametervärde.

-- Try to use a function as a parameter value.  
-- This produces an error message.  
EXEC dbo.uspGetWhereUsedProductID 819, GETDATE();  

Använd i stället en variabel för att skicka ett funktionsvärde till parametern, som i följande exempel:

-- Passing the function value as a variable.  
DECLARE @CheckDate datetime;  
SET @CheckDate = GETDATE();  
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;  
GO  

Ange parameternamn

När du skapar en procedur och deklarerar ett parameternamn måste parameternamnet börja med ett enda @ tecken och måste vara unikt i procedurens omfång.

Genom att uttryckligen namnge parametrarna och tilldela lämpliga värden till varje parameter i ett proceduranrop kan parametrarna anges i valfri ordning. Om proceduren till exempel my_proc förväntar sig tre parametrar med namnet @first, @secondoch @thirdkan värdena som skickas till proceduren tilldelas parameternamnen, till exempel: EXECUTE my_proc @second = 2, @first = 1, @third = 3;.

Notera

Om ett parametervärde anges i formuläret @parameter = valuemåste alla efterföljande parametrar anges på det här sättet. Om parametervärdena inte skickas i formuläret @parameter = valuemåste värdena anges i identisk ordning (från vänster till höger) eftersom parametrarna visas i create procedure-instruktionen. Det är en bra idé att ange parameternamn, både för överlägsen läsbarhet och kompatibilitet med framtida versioner av den lagrade proceduren.

Varning

Alla parametrar som skickas i formuläret @parameter = value med parametern felstavad får SQL Server att generera ett fel och förhindra procedurkörning.

Ange parameterdatatyper

Parametrar måste definieras med en datatyp när de deklareras i en CREATE PROCEDURE-instruktion. Datatypen för en parameter avgör vilken typ och intervall av värden som accepteras för parametern när proceduren anropas. Om du till exempel definierar en parameter med en liten datatyp godkänns endast numeriska värden mellan 0 och 255 när de skickas till parametern. Ett fel returneras om en procedur körs med ett värde som inte är kompatibelt med datatypen.

Ange standardvärden för parametern

En parameter anses vara valfri om parametern har ett standardvärde som anges när den deklareras. Det är inte nödvändigt att ange ett värde för en valfri parameter i ett proceduranrop.

Standardvärdet för en parameter används när:

• Inget värde för parametern anges i proceduranropet.
• Nyckelordet DEFAULT anges som värdet i proceduranropet.

Not

Om standardvärdet är en teckensträng som innehåller inbäddade blanksteg eller skiljetecken, eller om det börjar med ett tal (till exempel 6abc), måste det omges av enkla, raka citattecken.

Note

Standardparametrar stöds inte i Azure Synapse Analytics eller Analytics Platform System (PDW).

Om inget värde kan anges på lämpligt sätt som standard för parametern anger du NULL som standard. Det är en bra idé att proceduren returnerar ett anpassat meddelande om proceduren körs utan ett värde för parametern.

I följande exempel skapas proceduren uspGetSalesYTD med en indataparameter, @SalesPerson. NULL tilldelas som standardvärde för parametern och används i felhanteringsinstruktioner för att returnera ett anpassat felmeddelande i fall då proceduren körs utan ett värde för parametern @SalesPerson.

USE AdventureWorks2022;  
GO  
IF OBJECT_ID('Sales.uspGetSalesYTD', 'P') IS NOT NULL  
    DROP PROCEDURE Sales.uspGetSalesYTD;  
GO  
CREATE PROCEDURE Sales.uspGetSalesYTD  
    @SalesPerson nvarchar(50) = NULL  -- NULL default value  
AS   
    SET NOCOUNT ON;   
  
-- Validate the @SalesPerson parameter.  
IF @SalesPerson IS NULL  
BEGIN  
   PRINT 'ERROR: You must specify the last name of the sales person.'  
   RETURN  
END  
-- Get the sales for the specified sales person and   
-- assign it to the output parameter.  
SELECT SalesYTD  
FROM Sales.SalesPerson AS sp  
JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID  
WHERE LastName = @SalesPerson;  
RETURN  
GO  

I följande exempel körs proceduren. Den första instruktionen kör proceduren utan att ange ett indatavärde. Detta gör att felhanteringsinstruktionerna i proceduren returnerar det anpassade felmeddelandet. Den andra instruktionen tillhandahåller ett indatavärde och returnerar den förväntade resultatuppsättningen.

-- Run the procedure without specifying an input value.  
EXEC Sales.uspGetSalesYTD;  
GO  
-- Run the procedure with an input value.  
EXEC Sales.uspGetSalesYTD N'Blythe';  
GO  

Även om parametrar för vilka standardvärden har angetts kan utelämnas, kan listan över parametrar som inte kan nulleras bara trunkeras. Om en procedur till exempel har fem parametrar, utan att ange parameternamnen med en @parameter = value, kan den fjärde och den femte parametrarna utelämnas. Den fjärde parametern kan dock inte utelämnas så länge den femte parametern ingår, om inte parametrarna anges i formatet @parameter = value.

Ange flera parametrar med standardvärden

Du kan utelämna parametrar om du anger parametrarnas namn. Överväg följande lagrade procedur med flera valfria parametrar med NULL standardvärden.

USE AdventureWorks2022;
GO
IF OBJECT_ID ( 'Production.uspSearchList', 'P' ) IS NOT NULL   
    DROP PROCEDURE Production.uspSearchList;  
GO  
CREATE PROCEDURE Production.uspSearchList
      @ListPrice money 
    , @ProductCategoryID int       = NULL  -- NULL default value  
    , @ProductSubcategoryID int    = NULL  -- NULL default value  
    , @ProductBusinessEntityID int = NULL  -- NULL default value  
AS  
    SET NOCOUNT ON;  
    SELECT 
        p.Name, p.Class, p.ListPrice, p.ProductID, pc.Name, psc.Name, v.Name
    FROM 
        Production.Product AS p
    INNER JOIN Production.ProductSubCategory AS psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID
    INNER JOIN Production.ProductCategory AS pc ON psc.ProductCategoryID = pc.ProductCategoryID
    INNER JOIN Purchasing.ProductVendor AS pv ON p.ProductID = pv.ProductID
    INNER JOIN Purchasing.Vendor AS v ON pv.BusinessEntityID = v.BusinessEntityID
    WHERE (p.ListPrice < @ListPrice)
    AND   (pc.ProductCategoryID = @ProductCategoryID or @ProductCategoryID IS NULL)
    AND   (psc.ProductSubcategoryID = @ProductSubcategoryID or @ProductSubcategoryID IS NULL)
    AND   (pv.BusinessEntityID = @ProductBusinessEntityID or @ProductBusinessEntityID IS NULL);
GO

Du kan ange eller utelämna parametrar med standardvärden, som serien med följande exempel visar, så länge var och en har sitt parameternamn i formuläret @parameter = value:

--Find all Products with a list price less than 150.00 and in the ProductCategoryID = 4
EXEC Production.uspSearchList @ListPrice = 150, @ProductCategoryID = 4;
--Find all Products with a list price less than 150.00 and in the ProductSubCategoryID = 36
EXEC Production.uspSearchList @ListPrice = 150, @ProductSubCategoryID = 36;
--Find all Products with a list price less than 150.00 and from @ProductBusinessEntityID = 1498
EXEC Production.uspSearchList @ListPrice = 150, @ProductBusinessEntityID = 1498;
--Find all Products with a list price less than 150.00 and in the ProductSubCategoryID = 36 and from @ProductBusinessEntityID = 1498
EXEC Production.uspSearchList @ListPrice = 150, @ProductCategoryID = 4, @ProductBusinessEntityID = 1498;

Följande exempel är inte giltig T-SQL-syntax eftersom alla efterföljande parametrar måste anges på samma sätt när ett parameternamn har angetts. Att ange parameternamn för alla värden rekommenderas alltid och förhindrar fel och förvirring.

EXEC Production.uspSearchList @ListPrice = 150, 4, 1498;

Ange parameterriktning

Riktningen för en parameter är antingen indata, ett värde skickas till brödtexten i proceduren eller utdata, proceduren returnerar ett värde till det anropande programmet. Standardvärdet är en indataparameter.

Om du vill ange en utdataparameter måste nyckelordet OUTPUT anges i definitionen av parametern i INSTRUKTIONEN SKAPA. Proceduren returnerar det aktuella värdet för utdataparametern till det anropande programmet när proceduren avslutas. Det anropande programmet måste också använda nyckelordet OUTPUT när du kör proceduren för att spara parameterns värde i en variabel som kan användas i det anropande programmet.

I följande exempel skapas proceduren Production.usp_GetList, som returnerar en lista över produkter som har priser som inte överskrider ett angivet belopp. Exemplet visar hur du använder flera SELECT-instruktioner och flera OUTPUT-parametrar. OUTPUT-parametrar tillåter en extern procedur, en batch eller mer än en Transact-SQL-instruktion att få åtkomst till ett värde som ställts in under procedurens körning.

USE AdventureWorks2022;  
GO  
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL   
    DROP PROCEDURE Production.uspGetList;  
GO  
CREATE PROCEDURE Production.uspGetList 
      @Product varchar(40)   
    , @MaxPrice money   
    , @ComparePrice money OUTPUT  
    , @ListPrice money OUT  
AS  
    SET NOCOUNT ON;  
    SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'  
    FROM Production.Product AS p  
    JOIN Production.ProductSubcategory AS s   
      ON p.ProductSubcategoryID = s.ProductSubcategoryID  
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;  
-- Populate the output variable @ListPprice.  
SET @ListPrice = (SELECT MAX(p.ListPrice)  
        FROM Production.Product AS p  
        JOIN  Production.ProductSubcategory AS s   
          ON p.ProductSubcategoryID = s.ProductSubcategoryID  
        WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);  
-- Populate the output variable @compareprice.  
SET @ComparePrice = @MaxPrice;  
GO  

Kör usp_GetList för att returnera en lista över Adventure Works-produkter (Bikes) som kostar mindre än 700 USD. Output-parametrarna @cost och @compareprices används med kontroll av flödesspråk för att returnera ett meddelande i fönstret Meddelanden.

Not

Variabeln OUTPUT måste definieras när proceduren skapas och även under användningen av variabeln. Parameternamnet och variabelnamnet behöver inte matcha. Datatypen och parameterpositioneringen måste dock matcha (såvida inte @listprice = variable används).

DECLARE @ComparePrice money, @Cost money ;  
EXECUTE Production.uspGetList '%Bikes%', 700,   
    @ComparePrice OUT,   
    @Cost OUTPUT  
IF @Cost <= @ComparePrice   
BEGIN  
    PRINT 'These products can be purchased for less than   
    $'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'  
END  
ELSE  
    PRINT 'The prices for all products in this category exceed   
    $'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.';  
  

Här är det delvisa resultatet:

Product                                            List Price  
-------------------------------------------------- ------------------  
Road-750 Black, 58                                 539.99  
Mountain-500 Silver, 40                            564.99  
Mountain-500 Silver, 42                            564.99  
...  
Road-750 Black, 48                                 539.99  
Road-750 Black, 52                                 539.99  
  
(14 row(s) affected)  
  
These items can be purchased for less than $700.00.  

Relaterat innehåll

• CREATE PROCEDURE (Transact-SQL)
• parametrar
• EXECUTE (Transact-SQL)