Especificar parâmetros em um procedimento armazenado
Aplica-se a:
Banco de Dados SQL do AzureBanco de Dados Azure SQLInstância Gerenciada SQL do AzureAzure Synapse AnalyticsAnalytics Platform System (PDW)Banco de Dados SQL no Microsoft Fabric
Ao especificar parâmetros de procedimento, os programas de chamada são capazes de passar valores para o corpo do procedimento. Esses valores podem ser usados para uma variedade de finalidades durante a execução do procedimento. Os parâmetros de procedimento também podem retornar valores para o programa de chamada se o parâmetro estiver marcado como um parâmetro OUTPUT.
Um procedimento pode ter um máximo de 2100 parâmetros; Cada um atribuiu um nome, tipo de dados e direção. Opcionalmente, os parâmetros podem ser atribuídos valores padrão.
A seção a seguir fornece informações sobre como passar valores em parâmetros e sobre como cada um dos atributos de parâmetro é usado durante uma chamada de procedimento.
Observação
Consulte a série AdventureWorks de bancos de dados de exemplo para os exercícios deste artigo. Para obter mais informações, consulte bancos de dados de exemplo do AdventureWorks.
Passar valores em parâmetros
Os valores dos parâmetros fornecidos com uma chamada de procedimento devem ser constantes ou uma variável; Um nome de função não pode ser usado como um valor de parâmetro. As variáveis podem ser definidas pelo usuário ou variáveis do sistema, como @@spid.
Os exemplos a seguir demonstram a passagem de valores de parâmetros para o procedimento uspGetWhereUsedProductID. Eles ilustram como passar parâmetros como constantes e variáveis e também como usar uma variável para passar o valor de uma função.
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
O exemplo a seguir retorna um erro porque uma função não pode ser passada como um valor de parâmetro.
-- Try to use a function as a parameter value.
-- This produces an error message.
EXEC dbo.uspGetWhereUsedProductID 819, GETDATE();
Em vez disso, use uma variável para passar um valor de função para o parâmetro, como no exemplo a seguir:
-- Passing the function value as a variable.
DECLARE @CheckDate datetime;
SET @CheckDate = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;
GO
Especificar nomes de parâmetros
Ao criar um procedimento e declarar um nome de parâmetro, o nome do parâmetro deve começar com um único caractere @ e deve ser exclusivo no escopo do procedimento.
Nomear explicitamente os parâmetros e atribuir os valores apropriados a cada parâmetro em uma chamada de procedimento permite que os parâmetros sejam fornecidos em qualquer ordem. Por exemplo, se o procedimento my_proc espera três parâmetros chamados @first, @seconde @third, os valores passados para o procedimento podem ser atribuídos aos nomes dos parâmetros, como: EXECUTE my_proc @second = 2, @first = 1, @third = 3;.
Observação
Se um valor de parâmetro for fornecido na forma @parameter = value, todos os parâmetros subsequentes devem ser fornecidos dessa maneira. Se os valores dos parâmetros não forem passados na forma @parameter = value, os valores devem ser fornecidos na mesma ordem (da esquerda para a direita) como os parâmetros são listados na instrução CREATE PROCEDURE. É uma boa prática especificar nomes de parâmetros, tanto para uma legibilidade superior quanto para compatibilidade com versões futuras do procedimento armazenado.
Advertência
Qualquer parâmetro passado no formulário @parameter = value com o parâmetro escrito incorretamente, fará com que o SQL Server gere um erro e impeça a execução do procedimento.
Especificar tipos de dados de parâmetros
Os parâmetros devem ser definidos com um tipo de dados quando são declarados em uma instrução CREATE PROCEDURE. O tipo de dados de um parâmetro determina o tipo e o intervalo de valores que são aceitos para o parâmetro quando o procedimento é chamado. Por exemplo, se você definir um parâmetro com um tipo de dados tinyint, somente valores numéricos que variam de 0 a 255 serão aceitos quando passados para esse parâmetro. Um erro é retornado se um procedimento é executado com um valor incompatível com o tipo de dados.
Especificar valores padrão de parâmetro
Um parâmetro é considerado opcional se tiver um valor padrão especificado quando for declarado. Não é necessário fornecer um valor para um parâmetro opcional em uma chamada de procedimento.
O valor padrão de um parâmetro é usado quando:
- Nenhum valor para o parâmetro é especificado na chamada de procedimento.
- A palavra-chave DEFAULT é especificada como o valor na chamada de procedimento.
Observação
Se o valor padrão for uma cadeia de caracteres que contenha espaços em branco ou pontuação incorporados, ou se começar com um número (por exemplo, 6abc), ele deverá ser colocado entre aspas simples e retas.
Observação
Os parâmetros padrão não são suportados no Azure Synapse Analytics ou no Analytics Platform System (PDW).
Se nenhum valor puder ser especificado adequadamente como padrão para o parâmetro, especifique NULL como padrão. É uma boa ideia fazer com que o procedimento retorne uma mensagem personalizada se o procedimento for executado sem um valor para o parâmetro.
O exemplo a seguir cria o procedimento uspGetSalesYTD com um parâmetro de entrada, @SalesPerson.
NULL é atribuído como o valor padrão para o parâmetro e é usado em instruções de tratamento de erros para retornar uma mensagem de erro personalizada para casos em que o procedimento é executado sem um valor para o parâmetro @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
O exemplo a seguir executa o procedimento. A primeira instrução executa o procedimento sem especificar um valor de entrada. Isso faz com que as instruções de tratamento de erro no procedimento retornem a mensagem de erro personalizada. A segunda instrução fornece um valor de entrada e retorna o conjunto de resultados esperados.
-- 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
Embora os parâmetros para os quais os valores padrão estão definidos possam ser omitidos, a lista de parâmetros não nulos apenas pode ser truncada. Por exemplo, se um procedimento tiver cinco parâmetros, sem especificar os nomes dos parâmetros com um @parameter = value, o quarto e o quinto parâmetros podem ser omitidos. No entanto, o quarto parâmetro não pode ser ignorado, desde que o quinto parâmetro esteja incluído, a menos que os parâmetros sejam fornecidos na forma @parameter = value.
Especificar vários parâmetros com valores padrão
Você pode omitir parâmetros se especificar os nomes dos parâmetros. Considere o seguinte procedimento armazenado com vários parâmetros opcionais com valores padrão de 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
Você pode especificar ou omitir parâmetros com valores padrão, como demonstra a série de exemplos a seguir, desde que cada um seja fornecido com seu nome de parâmetro no formato @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;
O exemplo a seguir não é uma sintaxe T-SQL válida, porque todos os parâmetros subsequentes devem ser fornecidos da mesma maneira, uma vez que um nome de parâmetro é fornecido. O fornecimento de nomes de parâmetros para todos os valores é sempre recomendado e evita erros e confusão.
EXEC Production.uspSearchList @ListPrice = 150, 4, 1498;
Especificar a direção do parâmetro
A direção de um parâmetro pode ser de entrada, onde um valor é passado para o corpo do procedimento, ou de saída, onde o procedimento retorna um valor para o programa chamador. O padrão é um parâmetro de entrada.
Para especificar um parâmetro de saída, a palavra-chave OUTPUT deve ser especificada na definição do parâmetro na instrução CREATE PROCEDURE. O procedimento retorna o valor atual do parâmetro de saída para o programa de chamada quando o procedimento é encerrado. O programa de chamada também deve usar a palavra-chave OUTPUT ao executar o procedimento para salvar o valor do parâmetro em uma variável que pode ser usada no programa de chamada.
O exemplo a seguir cria o procedimento Production.usp_GetList, que retorna uma lista de produtos com preços que não excedem um valor especificado. O exemplo mostra o uso de várias instruções SELECT e vários parâmetros OUTPUT. Os parâmetros OUTPUT permitem que um procedimento externo, um lote ou mais de uma instrução Transact-SQL acesse um conjunto de valores durante a execução do procedimento.
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
Execute usp_GetList para devolver uma lista de produtos Adventure Works (Bikes) que custam menos de $700. Os parâmetros OUTPUT @cost e @compareprices são usados com linguagem de controle de fluxo de execução para retornar uma mensagem na janela de Mensagens .
Observação
A variável OUTPUT deve ser definida durante a criação do procedimento e também durante o uso da variável. O nome do parâmetro e o nome da variável não precisam corresponder. No entanto, o tipo de dados e o posicionamento dos parâmetros devem corresponder (a menos que @listprice = variable seja usado).
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)))+'.';
Aqui está o conjunto de resultados parciais:
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.