Указание параметров в хранимой процедуре

Применимо: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure azure Synapse Analytics Analytics Platform System (PDW)

Путем указания параметров процедуры вызывающие программы могут передавать значения в тело процедуры. Эти значения могут использоваться для разных целей во время исполнения процедуры. Параметры процедуры могут также возвращать значения вызывающей программе, если параметр помечен признаком OUTPUT.

Хранимая процедура может иметь не более 2100 параметров, каждый из которых имеет имя, тип данных и направление. При необходимости параметрам можно задавать значения по умолчанию.

В следующем разделе содержатся сведения о передаче значений параметрам и о том, как каждый из атрибутов параметров используется во время вызова процедуры.

Передача значений в параметры

Значения параметра, переданные при вызове процедуры, должны быть константами или переменными. Имя функции не может быть значением параметра. Переменные могут быть определяемыми пользователем или системными переменными, такими как @@spid.

В следующих примерах демонстрируется передача значений параметров процедуре uspGetWhereUsedProductID. В них показано, как передать в качестве параметров константы и переменные, а также как использовать переменную для передачи значения функции.

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

В следующем примере возвращается ошибка, так как функция не может быть передана в качестве значения параметра.

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

Вместо этого используйте переменную для передачи значения функции параметру, как показано в следующем примере:

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

Указание имен параметров

При создании процедуры и объявлении имени параметра, последнее должно начинаться с единичного символа @ и быть уникальным для всей процедуры.

Явные имена параметров и задание значений каждому параметру в процедуре позволяет передавать параметры в любом порядке. Например, если процедура my_proc ожидает три параметра с именем @first, @secondа @thirdтакже значения, передаваемые процедуре, можно назначить именам параметров, например: EXECUTE my_proc @second = 2, @first = 1, @third = 3;

Указание типов данных параметров

Параметры должны быть определены с типом данных в момент объявления в инструкции CREATE PROCEDURE. Тип данных параметра определяет тип и диапазон допустимых значений параметра при вызове процедуры. Например, параметр типа tinyint может принимать только численные значения в диапазоне от 0 до 255 в момент передачи этому параметру. При попытке выполнить процедуру со значением, не совместимым с типом данных, происходит ошибка.

Указание значений параметров по умолчанию

Параметр считается необязательным, если он имеет значение по умолчанию при объявлении. Нет необходимости указывать значение необязательного параметра при вызове процедуры.

Значение параметра по умолчанию используется, когда:

• не указано значение для параметра при вызове процедуры.
• в качестве значения при вызове процедуры указывается ключевое слово DEFAULT.

Если значение не может быть указано соответствующим образом в качестве значения по умолчанию для параметра, укажите NULL значение по умолчанию. Желательно, чтобы процедура возвращала сообщение, если она выполняется без значения для параметра.

В следующем примере создается процедура uspGetSalesYTD с единственным входным параметром @SalesPerson. NULL назначается в качестве значения по умолчанию для параметра и используется в инструкциях обработки ошибок для возврата пользовательского сообщения об ошибке в случаях, когда процедура выполняется без значения параметра @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  

Следующий пример выполняет процедуру. Первая инструкция выполняет процедуру без указания входного значения. В результате чего инструкции обработки ошибок процедуры возвращают пользовательское сообщение об ошибке. Вторая инструкция задает входное значение и возвращает ожидаемый результирующий набор.

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

Хотя параметры, для которых были предоставлены значения по умолчанию, могут быть опущены, список параметров, не допускающих значение NULL, может быть усечен только. Например, если процедура имеет пять параметров, не указывая имена параметров с параметром @parameter = value, четвертый и пятый параметры могут быть опущены. Однако четвертый параметр нельзя пропустить до тех пор, пока пятый параметр не включен, если параметры не указаны в форме @parameter = value.

Указание нескольких параметров со значениями по умолчанию

Если указать имена параметров, можно опустить параметры. Рассмотрим следующую хранимую процедуру с несколькими необязательными параметрами со значениями 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

Можно указать или опустить параметры со значениями по умолчанию, как показано в серии следующих примеров, если каждый из них указан с его именем параметра в форме @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;

В следующем примере недействителен синтаксис T-SQL, так как все последующие параметры должны быть предоставлены таким же образом, после предоставления имени параметра. Всегда рекомендуется предоставлять имена параметров для всех значений и предотвращать ошибки и путаницу.

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

Указание направления параметров

Параметр может быть как входным, когда значение передается в тело процедуры, так и выходным, возвращаемым процедурой вызывающей программе. По умолчанию параметр определен как входной.

Для указания выходного параметра в определении процедуры необходимо указать ключевое слово OUTPUT в инструкции CREATE PROCEDURE. Процедура, завершая свою работу, возвращает текущее значение выходного параметра в вызывающую программу. При выполнении процедуры вызывающая программа также должна использовать ключевое слово OUTPUT для сохранения значения параметра в переменной, которое затем может быть использовано в вызывающей программе.

В следующем примере создается процедура Production.usp_GetList, которая возвращает список продуктов, цены которых не превышают заданного значения. На данном примере демонстрируется использование нескольких инструкций SELECT и нескольких параметров OUTPUT. Параметры OUTPUT позволяют внешней процедуре, пакету или нескольким операторам 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  

Выполните, usp_GetList чтобы вернуть список продуктов Adventure Works (Bikes), которые стоят менее $ 700. Параметры @cost и @compareprices типа OUTPUT используются в языке управления выполнением для вывода информации в окне Сообщения .

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

Частичный результирующий набор:

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)
• Параметры
• EXECUTE (Transact-SQL)