Указание параметров в хранимой процедуре
Применимо:
SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure базе данных SQL Azure Synapse Analytics Analytics Platform System (PDW) в Microsoft Fabric
Путем указания параметров процедуры вызывающие программы могут передавать значения в тело процедуры. Эти значения могут использоваться для разных целей во время исполнения процедуры. Параметры процедуры могут также возвращать значения вызывающей программе, если параметр помечен признаком 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.