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

Статья
05/24/2023

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

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

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

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

Примечание

См. AdventureWorks серию примеров баз данных для упражнений этой статьи. Дополнительные сведения см. в разделе Образцы баз данных AdventureWorks.

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

Значения параметра, переданные при вызове процедуры, должны быть константами или переменными. Имя функции не может быть значением параметра. Переменные могут быть определяемыми пользователем или системными переменными, такими как @@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;.

Примечание

Если одно значение параметра указано в виде @parameter = value, все последующие параметры должны быть предоставлены таким образом. Если значения параметров не передаются в форме @parameter = value, значения должны быть предоставлены в том же порядке (слева направо), что и параметры, перечисленные в инструкции CREATE PROCEDURE. Рекомендуется указывать имена параметров как для удобства чтения, так и для совместимости с будущими версиями хранимой процедуры.

Предупреждение

Любой параметр, переданный в форме @parameter = value с ошибкой в параметре, вызовет ошибку SQL Server и предотвратит выполнение процедуры.

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

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

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

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

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

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

Примечание

Если значением по умолчанию является символьная строка, содержащая внедренные пробелы или знаки препинания, или если оно начинается с числа (например, 6abc), оно должно быть заключено в одинарные прямые кавычки.

Примечание

Параметры по умолчанию не поддерживаются в Azure Synapse Analytics или Analytics Platform System (PDW).

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

Примечание

Переменная OUTPUT должна быть определена во время создания процедуры, а также в ходе использования переменной. Имена параметра и переменной не должны совпадать. Однако тип данных и расположение параметров должны совпадать (если @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)))+'.';

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

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.

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

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

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

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

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

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

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

Дальнейшие действия

Обратная связь

Обратная связь

Дополнительные ресурсы