Zadání parametrů v uložené proceduře

Článek
03/05/2025

Platí pro:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)databáze SQL v Microsoft Fabric

Zadáním parametrů procedury mohou volající programy předávat hodnoty do těla procedury. Tyto hodnoty je možné použít pro různé účely během provádění procedury. Parametry procedury můžou také vracet hodnoty volajícímu programu, pokud je parametr označený jako parametr OUTPUT.

Procedura může mít maximálně 2100 parametrů; každý přiřazený název, datový typ a směr. Volitelně je možné přiřadit výchozí hodnoty parametrů.

Následující část obsahuje informace o předávání hodnot do parametrů a o tom, jak se jednotlivé atributy parametrů používají během volání procedury.

Poznámka

Podívejte se na sérii ukázkových databází AdventureWorks k procvičování v tomto článku. Další informace naleznete v ukázkových databázích AdventureWorks .

Předání hodnot do parametrů

Hodnoty parametrů zadané voláním procedury musí být konstanty nebo proměnné; Název funkce nelze použít jako hodnotu parametru. Proměnné mohou být uživatelem definované nebo systémové proměnné, jako je @@spid.

Následující příklady ukazují předávání hodnot parametrů do procedury uspGetWhereUsedProductID. Ilustrují, jak předat parametry jako konstanty a proměnné a také jak použít proměnnou k předání hodnoty funkce.

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

Následující příklad vrátí chybu, protože funkci nelze předat jako hodnotu parametru.

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

Místo toho použijte proměnnou k předání hodnoty funkce parametru, jako v následujícím příkladu:

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

Zadání názvů parametrů

Při vytváření procedury a deklarování názvu parametru musí název parametru začínat jediným znakem @ a musí být jedinečný v oboru procedury.

Explicitní pojmenování parametrů a přiřazení příslušných hodnot každému parametru ve volání procedury umožňuje zadat parametry v libovolném pořadí. Pokud například procedura my_proc očekává tři parametry s názvem @first, @seconda @third, mohou být hodnoty předané procedurě přiřazeny názvům parametrů, například: EXECUTE my_proc @second = 2, @first = 1, @third = 3;.

Poznámka

Pokud je ve formuláři @parameter = valuezadána jedna hodnota parametru, musí být tímto způsobem zadány všechny následující parametry. Pokud hodnoty parametrů nejsou předány ve formuláři @parameter = value, musí být hodnoty zadány ve stejném pořadí (zleva doprava), protože parametry jsou uvedeny v příkazu CREATE PROCEDURE. Pro zajištění lepší čitelnosti a kompatibility s budoucími verzemi uložené procedury je vhodné zadat názvy parametrů.

Varování

Jakýkoli parametr předaný ve formuláři @parameter = value s chybným pravopisem parametru způsobí, že SQL Server vygeneruje chybu a zabrání spuštění procedury.

Zadání datových typů parametrů

Parametry musí být definovány s datovým typem, pokud jsou deklarovány v příkazu CREATE PROCEDURE. Datový typ parametru určuje typ a rozsah hodnot, které jsou pro parametr přijaty při volání procedury. Pokud například definujete parametr s datovým typem tinyint, při předání do tohoto parametru se přijímají pouze číselné hodnoty v rozsahu od 0 do 255. Pokud je procedura spuštěna s hodnotou nekompatibilní s datovým typem, vrátí se chyba.

Zadání výchozích hodnot parametrů

Parametr je považován za volitelný, pokud má parametr výchozí hodnotu zadanou při deklaraci. V volání procedury není nutné zadat hodnotu volitelného parametru.

Výchozí hodnota parametru se používá v těchto případech:

Ve volání procedury není zadána žádná hodnota parametru.
Klíčové slovo DEFAULT je zadáno jako hodnota ve volání procedury.

Poznámka

Pokud je výchozí hodnota znakový řetězec, který obsahuje vložené prázdné znaky nebo interpunkci, nebo pokud začíná číslem (například 6abc), musí být uzavřen v jednoduchých, rovných uvozovkách.

Poznámka

Výchozí parametry se nepodporují ve službě Azure Synapse Analytics ani v systému PDW (Analytics Platform System).

Pokud pro parametr nelze správně zadat žádnou hodnotu, zadejte jako výchozí NULL. Pokud se procedura provede bez hodnoty parametru, je vhodné, aby procedura vrátila přizpůsobenou zprávu.

Následující příklad vytvoří uspGetSalesYTD proceduru s jedním vstupním parametrem @SalesPerson. NULL je přiřazena jako výchozí hodnota parametru a používá se v příkazech pro zpracování chyb k vrácení vlastní chybové zprávy pro případy, kdy se procedura provede bez hodnoty 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

Následující příklad provede proceduru. První příkaz provede proceduru bez zadání vstupní hodnoty. To způsobí, že mechanismy zpracování chyb v postupu vrátí vlastní chybovou zprávu. Druhý příkaz poskytuje vstupní hodnotu a vrátí očekávanou sadu výsledků.

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

I když je možné vynechat parametry, pro které byly zadány výchozí hodnoty, seznam parametrů, které nemohou mít hodnotu null, lze zkrátit pouze. Pokud má například procedura pět parametrů bez zadání názvů parametrů pomocí @parameter = value, čtvrtý a pátý parametr lze vynechat. Čtvrtý parametr však nelze přeskočit, pokud je zahrnutý pátý parametr, pokud nejsou parametry zadány ve formuláři @parameter = value.

Zadání více parametrů s výchozími hodnotami

Pokud zadáte názvy parametrů, můžete parametry vynechat. Zvažte následující uloženou proceduru s několika volitelnými parametry s výchozími hodnotami 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

Můžete zadat nebo vynechat parametry s výchozími hodnotami, jak ukazuje řada následujících příkladů, pokud je každý zadaný s názvem parametru ve formuláři @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;

Následující příklad není platná syntaxe T-SQL, protože všechny následující parametry musí být zadány stejným způsobem, jakmile je zadán název parametru. Zadávání názvů parametrů pro všechny hodnoty se vždy doporučuje a zabraňuje chybám a nejasnostem.

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

Zadat směr parametru

Směr parametru může být buď vstup, kdy se hodnota předává do těla procedury, nebo výstup, kdy procedura vrací hodnotu programu, který ji volá. Výchozí hodnota je vstupní parametr.

Chcete-li zadat výstupní parametr, musí být klíčové slovo OUTPUT zadáno v definici parametru v příkazu CREATE PROCEDURE. Procedura vrátí aktuální hodnotu výstupního parametru volajícímu programu při ukončení procedury. Volající program musí při provádění procedury také použít klíčové slovo OUTPUT, aby se hodnota parametru uložila do proměnné, kterou lze použít v volajícím programu.

Následující příklad vytvoří Production.usp_GetList postup, který vrátí seznam produktů, které mají ceny, které nepřekračují zadanou částku. Příklad ukazuje použití více příkazů SELECT a více parametrů OUTPUT. Parametry OUTPUT umožňují přístup k hodnotě nastavené během provádění procedury pro externí proceduru, dávkový proces nebo více příkazů Transact-SQL.

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

Spuštěním usp_GetList vrátíte seznam produktů Adventure Works (Bikes), které stojí méně než 700 USD. Parametry OUTPUT @cost a @compareprices se používají s jazykem řízení toku pro vrácení zprávy v okně Zprávy .

Poznámka

Proměnná OUTPUT musí být definována během vytváření procedury a také během použití proměnné. Název parametru a název proměnné se nemusí shodovat. Datový typ a umístění parametru se ale musí shodovat (pokud se nepoužívá @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)))+'.';

Tady je částečná sada výsledků:

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.

CREATE PROCEDURE (Transact-SQL)
parametrů
EXECUTE (Transact-SQL)

Sdílet prostřednictvím

Zadání parametrů v uložené proceduře

Předání hodnot do parametrů

Zadání názvů parametrů

Zadání datových typů parametrů

Zadání výchozích hodnot parametrů

Zadání více parametrů s výchozími hodnotami

Zadat směr parametru

Váš názor

Další materiály

Sdílet prostřednictvím

Zadání parametrů v uložené proceduře

Předání hodnot do parametrů

Zadání názvů parametrů

Zadání datových typů parametrů

Zadání výchozích hodnot parametrů

Zadání více parametrů s výchozími hodnotami

Zadat směr parametru

Související obsah

Váš názor

Další materiály