Especificar parâmetros em um procedimento armazenado
Aplica-se a:
SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPDW (Analytics Platform System)
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
Consulte a AdventureWorks série de bancos de dados de exemplo para 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 variáveis do 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 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â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 chamados @first, @seconde @third, os valores passados para o procedimento podem ser atribuídos aos nomes de parâmetro, como: EXECUTE my_proc @second = 2, @first = 1, @third = 3;.
Observação
Se um valor de parâmetro for fornecido no formulário @parameter = value, todos os parâmetros subsequentes deverão ser fornecidos dessa maneira. Se os valores de parâmetro não forem passados no formulário @parameter = value, os valores deverão ser fornecidos na ordem idêntica (da esquerda para a direita), pois os parâmetros são listados na instrução CREATE PROCEDURE. É uma boa prática especificar nomes de parâmetro, tanto para legibilidade superior quanto para compatibilidade com versões futuras do procedimento armazenado.
Aviso
Qualquer parâmetro passado no formulário @parameter = value com o parâmetro escrito incorretamente fará com que SQL Server gere um erro e impeça 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
Se o valor padrão for uma cadeia de caracteres que contém espaços em branco inseridos ou pontuação, ou se ele começar com um número (por exemplo, 6abc), ele deverá ser colocado entre aspas simples e retas.
Observação
Não há suporte para parâmetros padrão no PDW (Analytics) do 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 o valor padrão para o parâmetro e é usado em instruções de tratamento de erro para retornar uma mensagem de erro personalizada para casos em que o procedimento é executado sem um valor para o @SalesPerson parâmetro .
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 os parâmetros para os quais os padrões foram fornecidos possam ser omitidos, a lista de parâmetros não anuláveis só pode ser truncada. Por exemplo, se um procedimento tiver cinco parâmetros, sem especificar os nomes de parâmetro com um @parameter = value, o quarto e o quinto parâmetros poderão ser omitidos. No entanto, o quarto parâmetro não pode ser ignorado desde que o quinto parâmetro seja incluído, a menos que os parâmetros sejam fornecidos no formulário @parameter = value.
Especificar vários parâmetros com valores padrão
Você poderá omitir parâmetros se especificar os nomes de parâmetros. Considere o procedimento armazenado a seguir com vários parâmetros opcionais com NULL valores padrão.
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 formulário @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, pois 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 é 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 adventure works (Bikes) que custam menos de US$ 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. No entanto, 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.
Próximas etapas
Comentários
Brevemente: Ao longo de 2024, vamos descontinuar progressivamente o GitHub Issues como mecanismo de feedback para conteúdos e substituí-lo por um novo sistema de feedback. Para obter mais informações, veja: https://aka.ms/ContentUserFeedback.
Submeter e ver comentários