Określ parametry w procedurze składowanej

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL Database w Microsoft Fabric

Określając parametry procedury, programy wywołujące mogą przekazywać wartości do treści procedury. Te wartości mogą być używane do różnych celów podczas wykonywania procedury. Parametry procedury mogą również zwracać wartości do programu wywołującego, jeśli parametr jest oznaczony jako parametr OUTPUT.

Procedura może mieć maksymalnie 2100 parametrów; każda przypisana nazwa, typ danych i kierunek. Opcjonalnie można przypisać wartości domyślne parametrów.

Poniższa sekcja zawiera informacje o przekazywaniu wartości do parametrów oraz o tym, jak każdy z atrybutów parametrów jest używany podczas wywołania procedury.

Notatka

Zapoznaj się z serią AdventureWorks przykładowych baz danych na potrzeby ćwiczeń tego artykułu. Aby uzyskać więcej informacji, zobacz przykładowe bazy danych AdventureWorks.

Przekazywanie wartości do parametrów

Wartości parametrów dostarczane z wywołaniem procedury muszą być stałe lub zmienna; nie można użyć nazwy funkcji jako wartości parametru. Zmienne mogą być zdefiniowane przez użytkownika lub zmienne systemowe, takie jak @@spid.

W poniższych przykładach pokazano przekazywanie wartości parametrów do procedury uspGetWhereUsedProductID. Ilustrują one sposób przekazywania parametrów jako stałych i zmiennych, a także jak używać zmiennej do przekazywania wartości funkcji.

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

Poniższy przykład zwraca błąd, ponieważ nie można przekazać funkcji jako wartości parametru.

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

Zamiast tego użyj zmiennej, aby przekazać wartość funkcji do parametru, jak w poniższym przykładzie:

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

Określanie nazw parametrów

Podczas tworzenia procedury i deklarowania nazwy parametru nazwa parametru musi zaczynać się od jednego znaku @ i musi być unikatowa w zakresie procedury.

Jawne nazewnictwo parametrów i przypisanie odpowiednich wartości do każdego parametru w wywołaniu procedury umożliwia dostarczenie parametrów w dowolnej kolejności. Jeśli na przykład procedura my_proc oczekuje trzech parametrów o nazwie @first, @secondi @third, wartości przekazane do procedury można przypisać do nazw parametrów, takich jak: EXECUTE my_proc @second = 2, @first = 1, @third = 3;.

Notatka

Jeśli jedna wartość parametru jest dostarczana w formularzu @parameter = value, wszystkie kolejne parametry muszą być podane w ten sposób. Jeśli wartości parametrów nie są przekazywane w formularzu @parameter = value, wartości muszą być podane w identycznej kolejności (od lewej do prawej), ponieważ parametry są wymienione w instrukcji CREATE PROCEDURE. Dobrym rozwiązaniem jest określenie nazw parametrów, zarówno w celu zapewnienia najwyższej czytelności, jak i zgodności z przyszłymi wersjami procedury składowanej.

Ostrzeżenie

Każdy parametr przekazany w formularzu @parameter = value z błędnie napisanym parametrem spowoduje wygenerowanie błędu programu SQL Server i uniemożliwienie wykonania procedury.

Określanie typów danych parametrów

Parametry muszą być zdefiniowane przy użyciu typu danych, gdy są zadeklarowane w instrukcji CREATE PROCEDURE. Typ danych parametru określa typ i zakres wartości, które są akceptowane dla parametru po wywołaniu procedury. Jeśli na przykład zdefiniujesz parametr z typem danych tinyint, podczas przekazywania do tego parametru akceptowane są tylko wartości liczbowe z zakresu od 0 do 255. Zwracany jest błąd, jeśli procedura jest wykonywana z wartością niezgodną z typem danych.

Określ wartości domyślne parametru

Parametr jest uznawany za opcjonalny, jeśli parametr ma wartość domyślną określoną po zadeklarowaniu. Nie jest konieczne podanie wartości opcjonalnego parametru w wywołaniu procedury.

Wartość domyślna parametru jest używana, gdy:

• W wywołaniu procedury nie określono żadnej wartości parametru.
• Słowo kluczowe DEFAULT jest określone jako wartość w wywołaniu procedury.

Notatka

Jeśli wartość domyślna to ciąg znaków, który zawiera osadzone wartości puste lub interpunkcyjne, lub jeśli zaczyna się od liczby (na przykład 6abc), musi być ujęta w pojedynczy, prosty cudzysłów.

Notatka

Parametry domyślne nie są obsługiwane w usłudze Azure Synapse Analytics lub Analytics Platform System (PDW).

Jeśli nie można odpowiednio określić żadnej wartości jako domyślnej dla parametru, określ NULL jako wartość domyślną. Dobrym pomysłem jest, aby procedura zwracała dostosowany komunikat, gdy jest uruchamiana bez wartości parametru.

Poniższy przykład tworzy procedurę uspGetSalesYTD z jednym parametrem wejściowym, @SalesPerson. NULL jest przypisywana jako wartość domyślna parametru i jest używana w instrukcjach obsługi błędów w celu zwrócenia niestandardowego komunikatu o błędzie w przypadkach, gdy procedura jest wykonywana bez wartości parametru @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  

Poniższy przykład wykonuje procedurę. Pierwsza instrukcja wykonuje procedurę bez określania wartości wejściowej. Instrukcje obsługi błędów w procedurze powodują zwrócenie niestandardowego komunikatu o błędzie. Druga instrukcja dostarcza wartość wejściową i zwraca oczekiwany zestaw wyników.

-- 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  

Mimo że parametry, dla których podano wartości domyślne, można pominąć, listę parametrów niemających wartości null można jedynie skrócić. Jeśli na przykład procedura ma pięć parametrów, bez określania nazw parametrów z @parameter = value, czwarty i piąty parametr można pominąć. Nie można jednak pominąć czwartego parametru tak długo, jak jest uwzględniony piąty parametr, chyba że parametry są podane w formularzu @parameter = value.

Określanie wielu parametrów z wartościami domyślnymi

Jeśli określisz nazwy parametrów, możesz pominąć parametry. Rozważ następującą procedurę składowaną z wieloma opcjonalnymi parametrami, które mają wartości domyślne NULL.

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

Można określić lub pominąć parametry z wartościami domyślnymi, jak pokazano w serii poniższych przykładów, o ile każda z nich jest dostarczana z nazwą parametru w formularzu @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;

Poniższy przykład nie jest prawidłową składnią języka T-SQL, ponieważ wszystkie kolejne parametry muszą być podane w taki sam sposób, po podaniu nazwy parametru. Podawanie nazw parametrów dla wszystkich wartości jest zawsze zalecane i zapobiega błędom i nieporozumieniu.

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

Określ kierunek parametru

Kierunek parametru to jest wejście, wartość jest przekazywana do treści procedury, lub wyjście, procedura zwraca wartość do programu wywołującego. Wartość domyślna to parametr wejściowy.

Aby określić parametr wyjściowy, słowo kluczowe OUTPUT musi być określone w definicji parametru w instrukcji CREATE PROCEDURE. Procedura zwraca bieżącą wartość parametru wyjściowego do programu wywołującego po zakończeniu procedury. Program wywołujący musi również użyć słowa kluczowego OUTPUT podczas wykonywania procedury w celu zapisania wartości parametru w zmiennej, która może być używana w programie wywołującym.

Poniższy przykład tworzy procedurę Production.usp_GetList, która zwraca listę produktów, które mają ceny, które nie przekraczają określonej kwoty. W przykładzie pokazano użycie wielu instrukcji SELECT i wielu parametrów OUTPUT. Parametry OUTPUT umożliwiają procedurom zewnętrznym, partiom lub więcej niż jednej instrukcji Transact-SQL dostęp do wartości ustawionej podczas wykonywania procedury.

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  

Wykonaj usp_GetList, aby zwrócić listę produktów Adventure Works (Bikes), które kosztują mniej niż 700 USD. Parametry WYJŚCIOWE @cost i @compareprices są używane z językiem sterowania przepływem, aby zwrócić komunikat w oknie wiadomości .

Nota

Zmienna OUTPUT musi być zdefiniowana podczas tworzenia procedury, a także podczas używania zmiennej. Nazwa parametru i nazwa zmiennej nie muszą być zgodne. Jednak typ danych i pozycjonowanie parametrów muszą być zgodne (chyba że jest używana @listprice = variable).

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)))+'.';  
  

Oto zestaw wyników częściowych:

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.  

Powiązana zawartość

• UTWÓRZ PROCEDURĘ (Transact-SQL)
• parametry
• WYKONAJ (Transact-SQL)