Criar procedimentos armazenados
Os procedimentos armazenados são uma das ferramentas mais poderosas do SQL Server para encapsular a lógica de negócios e melhorar o desempenho do aplicativo. Ao criar procedimentos armazenados, você cria blocos de código reutilizáveis que são executados no servidor, reduzindo o tráfego de rede e centralizando a lógica de acesso a dados.
Entenda os procedimentos armazenados
Um procedimento armazenado é uma coleção compilada de instruções T-SQL que o SQL Server armazena e executa como uma única unidade. Ao contrário das consultas não planejadas que você envia ao servidor todas as vezes, os procedimentos armazenados são pré-compilados e otimizados, o que significa que eles são executados mais rapidamente em execuções subsequentes.
Você usa procedimentos armazenados para encapsular lógica de negócios complexa, impor regras de validação de dados e controlar como os aplicativos interagem com seu banco de dados. Por exemplo, em vez de permitir o acesso direto à tabela, você pode criar procedimentos armazenados que validam a entrada, aplicam regras de negócios e registram alterações antes de modificar dados.
Os benefícios de desempenho vêm do cache do plano de consulta. Com consultas não planejadas, o SQL Server deve analisar e otimizar cada consulta todas as vezes. Com os procedimentos armazenados, o plano de execução é armazenado em cache após a primeira execução, reduzindo a sobrecarga para operações repetidas.
Criar procedimentos armazenados básicos
A criação de um procedimento armazenado começa com a instrução CREATE PROCEDURE seguida pela lógica T-SQL. Especifique o nome do procedimento usando um identificador qualificado por esquema, o que melhora a clareza e o desempenho.
CREATE PROCEDURE dbo.GetCustomerOrders
AS
BEGIN
SET NOCOUNT ON;
SELECT
OrderID,
CustomerID,
OrderDate,
TotalAmount
FROM dbo.Orders
ORDER BY OrderDate DESC;
END
A SET NOCOUNT ON instrução impede que a mensagem sobre o número de linhas afetadas seja enviada ao cliente. Isso reduz o tráfego de rede e melhora o desempenho, especialmente quando o procedimento executa várias instruções.
Ao criar procedimentos, use as palavras-chave BEGIN e END para definir claramente o corpo do procedimento. Isso torna seu código mais legível e ajuda a evitar erros ao adicionar ou modificar a lógica mais tarde.
Trabalhar com parâmetros
Os parâmetros tornam os procedimentos armazenados flexíveis e reutilizáveis. Você define parâmetros de entrada para aceitar valores do aplicativo de chamada e parâmetros de saída para retornar valores de volta ao chamador.
Os parâmetros de entrada usam o símbolo @ seguido por um nome de parâmetro e um tipo de dados. Você pode fornecer valores padrão para tornar os parâmetros opcionais:
CREATE PROCEDURE dbo.GetCustomerOrdersByDate
@CustomerID int,
@StartDate datetime = NULL,
@EndDate datetime = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT
OrderID,
OrderDate,
TotalAmount
FROM dbo.Orders
WHERE CustomerID = @CustomerID
AND (@StartDate IS NULL OR OrderDate >= @StartDate)
AND (@EndDate IS NULL OR OrderDate <= @EndDate)
ORDER BY OrderDate DESC;
END
Os parâmetros de saída permitem que você retorne valores ao aplicativo de chamada. Você os define usando a OUTPUT palavra-chave:
CREATE PROCEDURE dbo.CalculateOrderTotal
@OrderID int,
@TotalAmount decimal(10,2) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT @TotalAmount = SUM(Quantity * UnitPrice)
FROM dbo.OrderDetails
WHERE OrderID = @OrderID;
RETURN 0;
END
Ao chamar um procedimento com parâmetros de saída, você deve declarar uma variável para receber o valor e usar a OUTPUT palavra-chave na EXECUTE instrução.
Implementar o tratamento de erros
Procedimentos armazenados robustos incluem tratamento de erros para gerenciar condições inesperadas e manter a integridade dos dados. Você implementa o tratamento de erros usando TRY...CATCH blocos, que funcionam de forma semelhante ao tratamento de exceções em outras linguagens de programação.
CREATE PROCEDURE dbo.InsertCustomerOrder
@CustomerID int,
@OrderDate datetime,
@TotalAmount decimal(10,2)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
-- Validate customer exists
IF NOT EXISTS (SELECT 1 FROM dbo.Customers WHERE CustomerID = @CustomerID)
BEGIN
RAISERROR('Customer does not exist.', 16, 1);
END
-- Insert order
INSERT INTO dbo.Orders (CustomerID, OrderDate, TotalAmount)
VALUES (@CustomerID, @OrderDate, @TotalAmount);
COMMIT TRANSACTION;
RETURN 0;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
DECLARE @ErrorMessage nvarchar(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity int = ERROR_SEVERITY();
DECLARE @ErrorState int = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
RETURN -1;
END CATCH
END
O TRY bloco contém sua lógica principal, enquanto o CATCH bloco manipula todos os erros que ocorrem. Você pode usar funções do sistema como ERROR_MESSAGE(), ERROR_SEVERITY()e ERROR_STATE() capturar detalhes de erro e passá-los para o aplicativo de chamada.
Sempre verifique @@TRANCOUNT antes de reverter as transações no bloco CATCH. Isso evita erros se a transação já foi concluída ou nunca foi iniciada.
Aplicar melhores práticas
Seguir as práticas recomendadas estabelecidas ao criar procedimentos armazenados garante que eles sejam manteneveis, seguros e com desempenho.
Usar nomes qualificados por esquema
Use nomes qualificados por esquema para todos os objetos. Isso elimina a ambiguidade e melhora o desempenho evitando a sobrecarga de resolução de esquema:
-- Good
SELECT * FROM dbo.Orders
-- Avoid
SELECT * FROM Orders
Implementar validação de parâmetro
Implemente a validação de parâmetro no início do procedimento. Falha rapidamente quando as entradas são inválidas em vez de processar dados incorretos:
IF @CustomerID IS NULL OR @CustomerID <= 0
BEGIN
RAISERROR('CustomerID must be a positive integer.', 16, 1);
RETURN -1;
END
Evite SELECT *
Evite SELECT * no código de produção. Liste explicitamente colunas para evitar problemas quando as estruturas de tabela são alteradas e para melhorar o desempenho da consulta:
-- Good
SELECT OrderID, CustomerID, OrderDate FROM dbo.Orders
-- Avoid
SELECT * FROM dbo.Orders
Usar nomes significativos
Use nomes significativos que descrevem o que o procedimento faz. Inclua um verbo que indica a operação (Obter, Inserir, Atualizar, Excluir, Calcular):
CREATE PROCEDURE dbo.GetActiveCustomersByRegion
CREATE PROCEDURE dbo.UpdateCustomerAddress
CREATE PROCEDURE dbo.DeleteExpiredOrders
Evitar o sp_ prefixo
Não use o sp_ prefixo para seus procedimentos armazenados. O SQL Server reserva esse prefixo para procedimentos do sistema armazenados no master banco de dados. Quando você nomeia um procedimento com sp_, o SQL Server pesquisa master primeiro antes de verificar o banco de dados atual, adicionando sobrecarga desnecessária:
-- Good
CREATE PROCEDURE dbo.GetCustomerOrders
-- Avoid
CREATE PROCEDURE dbo.sp_GetCustomerOrders
A criação dessas práticas ajuda você a criar procedimentos armazenados que sua equipe possa entender, manter e confiar para executar de forma confiável em ambientes de produção.