Especificar parâmetros em um procedimento armazenado
Aplica-se a: 
SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics Analytics Platform System (PDW)
Ao especificar parâmetros de procedimento, programas de chamada podem passar valores para o corpo do procedimento. Esses valores podem ser usados com vários propósitos durante a execução do procedimento. Parâmetros de procedimento também retornam valores ao programa de chamada quando o parâmetro é marcado como um parâmetro OUTPUT.
Um procedimento pode ter no máximo 2100 parâmetros; a cada um é atribuído um nome, um tipo de dados e uma direção. Outra opção é atribuir valores padrão aos parâmetros.
A seção a seguir fornece informações sobre como passar valores para parâmetros e como cada atributo de parâmetro é usado durante uma chamada de procedimento.
Observação
Confira a série de bancos de dados de amostra AdventureWorks para acessar os exercícios deste artigo. Para obter mais informações, consulte os Bancos de dados de exemplo do AdventureWorks.
Passar valores para parâmetros
Os valores de 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 pelas variáveis de sistema como @@spid.
Os exemplos a seguir demonstram a passagem de valores de parâmetro 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 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âmetro
Na criação de um procedimento e declaração de um nome de parâmetro, o nome de parâmetro deve começar com um único caractere @ e deve ser exclusivo no escopo do procedimento.
A nomeação explícita dos parâmetros e a atribuição dos valores apropriados a cada parâmetro em uma chamada de procedimento permitem o fornecimento dos parâmetros em qualquer ordem. Por exemplo, se o procedimento my_proc espera três parâmetros com os nomes @first, @second e @third, os valores passados ao procedimento podem ser atribuídos aos nomes de parâmetros, como: EXECUTE my_proc @second = 2, @first = 1, @third = 3;.
Observação
Se um valor de parâmetro for fornecido no formato @parameter = value, forneça todos os parâmetros posteriores dessa maneira. Quando os valores de parâmetros não são passados no formato @parameter = value, os valores devem ser fornecidos na mesma ordem (da esquerda para a direita) que os parâmetros são listados na instrução CREATE PROCEDURE. É uma prática recomendada especificar nomes de parâmetros, tanto para melhorar a legibilidade quanto para garantir a compatibilidade com versões futuras do procedimento armazenado.
Aviso
Qualquer parâmetro transmitido no formato @parameter = value com o parâmetro digitado incorretamente fará com que o SQL Server gere um erro e impedirá a execução do procedimento.
Especificar tipos de dados de parâmetro
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 dos valores aceitos pelo parâmetro quando o procedimento é chamado. Por exemplo, se você definir um parâmetro com um tipo de dados tinyint , somente valores numéricos no intervalo entre 0 e 255 serão aceitos quando passados para esse parâmetro. Um erro será retornado se um procedimento for executado com um valor incompatível com o tipo de dados.
Especificar valores padrão de parâmetro
Um parâmetro é considerado opcional se o parâmetro tem um valor padrão especificado quando é 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 é especificado para o parâmetro na chamada do procedimento.
- A palavra-chave DEFAULT é especificada como o valor na chamada do procedimento.
Observação
Quando o valor padrão é uma cadeia de caracteres contendo pontuação ou espaços em branco inseridos, ou quando ela começa com um número (por exemplo, 6abc), ela deve ser colocada entre aspas simples.
Observação
Os parâmetros padrão não têm suporte no Azure Synapse Analytics ou no PDW (Analytics Platform System).
Se nenhum valor puder ser especificado adequadamente como um padrão para o parâmetro, especifique NULL como o padrão. Convém levar o procedimento a retornar uma mensagem personalizada se ele for executado sem um valor para o parâmetro.
O exemplo a seguir cria o procedimento armazenado uspGetSalesYTD com um parâmetro de entrada, @SalesPerson. NULL é atribuído como valor padrão para o parâmetro e é utilizado em instruções de tratamento de erros para retornar uma mensagem de erro personalizada nos casos de execução do procedimento 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 leva as instruções de tratamento de erros no procedimento a retornarem a mensagem de erro personalizada. A segunda instrução fornece um valor de entrada e retorna o conjunto de resultados esperado.
-- 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 seja possível omitir os parâmetros para os quais foram fornecidos padrões, a lista de parâmetros não anuláveis só poderá ser truncada. Por exemplo, quando um procedimento tem cinco parâmetros, sem especificar os nomes de parâmetro com um @parameter = value, o quarto e o quinto parâmetros podem ser omitidos. Entretanto, o quarto parâmetro não pode ser ignorado, contanto que o quinto parâmetro seja incluído, a menos que os parâmetros sejam fornecidos no formato @parameter = value.
Especificar vários parâmetros com valores padrão
É possível omitir parâmetros se especificar os nomes dos parâmetros. Considere o procedimento armazenado a seguir com vários parâmetros opcionais com valores padrão 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
É possível 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 é uma sintaxe T-SQL inválida, pois todos os parâmetros subsequentes devem ser fornecidos da mesma maneira, após fornecer um nome de parâmetro. É sempre recomendado fornecer nomes de parâmetro para todos os valores para evitar 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 é de entrada, em que um valor é passado para o corpo do procedimento armazenado, ou de saída, em que o procedimento retorna um valor ao programa de chamada. O padrão é um parâmetro de entrada.
Para especificar um parâmetro de saída, especifique a palavra-chave OUTPUT na definição do parâmetro na instrução CREATE PROCEDURE. O procedimento retorna o valor atual do parâmetro de saída ao programa de chamada quando o procedimento existe. O programa de chamada também deve usar a palavra-chave OUTPUT ao executar o procedimento a fim de 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 uma quantia especificada. 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 acessem um valor definido 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 retornar uma lista de produtos da Adventure Works (Bikes) que custam menos que USD 700. Os parâmetros OUTPUT @cost e @compareprices são usados com linguagem de controle de fluxo para retornar uma mensagem na janela 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 de parâmetro e o nome de variável não precisam coincidir. Contudo, o tipo de dados e o posicionamento do parâmetro 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)))+'.';
Este é o conjunto de resultados parcial:
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.