O SQL Server permite que os desenvolvedores armazenem, recuperem e manipulem dados usando a linguagem SQL. No entanto, à medida que os scripts e procedimentos armazenados se tornam mais complexos, gerenciar e manipular dados sem variáveis pode ser desafiador.
Sem variáveis, os desenvolvedores podem precisar repetir consultas complexas ou cálculos várias vezes dentro de um script, resultando em código inchado e difícil de ler, propenso a erros. Além disso, não usar variáveis pode dificultar o tratamento eficaz de erros, pois os desenvolvedores podem precisar repetir o mesmo código de tratamento de erros várias vezes dentro de um script. Isso pode resultar em código difícil de manter e torna mais difícil identificar e corrigir erros quando eles ocorrem.
Neste tutorial, discutiremos o conceito de uso de variáveis no SQL Server, juntamente com vários exemplos para demonstrar seu uso.
Declarando uma Única Variável SQL
A sintaxe T-SQL para declarar uma variável no SQL Server é a seguinte:
DECLARE @nome_da_variavel tipo_de_dados [= valor];
Por exemplo, vamos declarar uma variável chamada @ProductID
do tipo de dados inteiro e atribuir um valor estático de 778:
DECLARE @ProductID int = 778;
SELECT [SalesOrderID], [CarrierTrackingNumber], [OrderQty], [ProductID], [SpecialOfferID], [UnitPrice], [LineTotal]
FROM [AdventureWorks2019].[Sales].[SalesOrderDetail]
WHERE ProductID = @ProductID;
Você também pode atribuir o valor posteriormente usando a palavra-chave SET
:
DECLARE @ProductID int;
SET @ProductID = 778;
SELECT [SalesOrderID], [CarrierTrackingNumber], [OrderQty], [ProductID], [SpecialOfferID], [UnitPrice], [LineTotal]
FROM [AdventureWorks2019].[Sales].[SalesOrderDetail]
WHERE ProductID = @ProductID;
Você pode usar variáveis com diferentes tipos de dados, como caractere, data e decimal:
DECLARE @employee_name VARCHAR(50);
SET @employee_name = 'Rajendra';
DECLARE @current_date DATE = GETDATE();
DECLARE @tax_rate DECIMAL(4,2);
SET @tax_rate = 0.04;
Declarando Múltiplas Variáveis no SQL Server
Para declarar várias variáveis, você pode usar diferentes palavras-chave DECLARE
:
DECLARE @Nome varchar(50);
DECLARE @Idade tinyint;
DECLARE @DOJ int;
Alternativamente, você pode usar uma única palavra-chave DECLARE
e separar várias variáveis com uma vírgula:
DECLARE @Nome varchar(50), @Idade tinyint, @DOJ int;
Atribuindo Valores Dinâmicos às Variáveis
Você pode definir o valor de uma variável dinamicamente. Por exemplo, o código a seguir declara três variáveis, @AvgUnitPrice
, @AvgOrderQty
e @AvgLineTotal
. A instrução SELECT
calcula o preço unitário médio, a quantidade de pedidos média e o total da linha médio e atribui esses valores calculados às variáveis:
DECLARE @AvgUnitPrice int, @AvgOrderQty int, @AvgLineTotal int;
SELECT @AvgUnitPrice = Avg(UnitPrice), @AvgOrderQty = Avg(OrderQty), @AvgLineTotal = Avg(LineTotal)
FROM [AdventureWorks2019].[Sales].[SalesOrderDetail];
SELECT @AvgUnitPrice as AvgLineTotal, @AvgOrderQty as AvgOrderQty, @AvgLineTotal as AvgLineTotal;
Usando Variáveis em Procedimentos Armazenados
Variáveis em procedimentos armazenados fornecem flexibilidade e controle sobre a manipulação de dados. Por exemplo, o seguinte procedimento armazenado usa duas variáveis, @JobTitle
e @BirthDate
. O procedimento armazenado atribui valores a essas variáveis e imprime as informações necessárias usando essas variáveis:
CREATE PROCEDURE dbo.GetPersonInfo
@NationalIDNumber INT
AS
BEGIN
DECLARE @JobTitle VARCHAR(50);
DECLARE @BirthDate date;
SELECT @JobTitle = JobTitle, @BirthDate = BirthDate
FROM [AdventureWorks2019].[HumanResources].[Employee]
WHERE [NationalIDNumber] = @NationalIDNumber;
-- Use as variáveis para processamento adicional
PRINT 'Cargo: ' + @JobTitle;
PRINT 'Data de Nascimento: ' + CAST(@BirthDate AS VARCHAR(10));
END;
EXEC GetPersonInfo 295847284;
Usando Variáveis em Cursores
Variáveis podem ser usadas em cursores para armazenar e manipular dados. Por exemplo, a seguinte consulta declara um cursor, SalesOrder_Cursor
, e busca o número do pedido de venda, o total devido e o ID do vendedor do banco de dados AdventureWorks2019. O cursor itera pelos IDs de pedidos de venda, atribui as informações buscadas às variáveis @SalesOrderNumber
, @TotalDue
e @SalesPersonID
e imprime essas informações com mensagens específicas:
DECLARE @SalesOrderID int = 71951;
DECLARE @SalesOrderNumber varchar(20);
DECLARE @TotalDue DECIMAL(10, 2);
DECLARE @SalesPersonID varchar(20);
DECLARE SalesOrder_Cursor CURSOR FOR
SELECT SalesOrderNumber, TotalDue, SalesPersonID
FROM [AdventureWorks2019].[Sales].[SalesOrderHeader]
WHERE SalesOrderID >= @SalesOrderID AND SalesPersonID IS NOT NULL;
OPEN SalesOrder_Cursor;
FETCH NEXT FROM SalesOrder_Cursor INTO @SalesOrderNumber, @TotalDue, @SalesPersonID;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Realize operações usando as variáveis
PRINT 'ID do Vendedor: ' + @SalesPersonID;
PRINT 'Pedido de Venda: ' + CAST(@SalesOrderNumber AS VARCHAR(10));
PRINT 'Valor Total Devido: ' + CAST(@TotalDue AS VARCHAR(10));
-- Obtenha o próximo conjunto de dados do cursor
FETCH NEXT FROM SalesOrder_Cursor INTO @SalesOrderNumber, @TotalDue, @SalesPersonID;
END;
CLOSE SalesOrder_Cursor;
DEALLOCATE SalesOrder_Cursor;
Usando Variáveis para Criar Declarações SQL Dinâmicas
Variáveis podem ser usadas para criar declarações SQL dinâmicas combinando várias partes das consultas, como nomes de tabelas, parâmetros e argumentos. Por exemplo, o seguinte código cria e executa dinamicamente uma declaração SQL usando o procedimento armazenado sp_executesql
:
DECLARE @table_name VARCHAR(50) = 'SalesOrderHeader';
DECLARE @schema varchar(20) = 'Sales';
DECLARE @SalesOrderID varchar(20) = 71951;
DECLARE @sql_stmt NVARCHAR(MAX);
SET @sql_stmt = 'SELECT * FROM ' + @schema + '.' + QUOTENAME(@table_name) + ' WHERE SalesOrderID =' + @SalesOrderID;
EXEC sp_executesql @sql_stmt;
Usando Variáveis para Controlar a Execução de Loop
Variáveis podem ajudar a controlar a execução de loops definindo condições, controlando o contador do loop e determinando quando o loop deve continuar ou sair. Por exemplo, o seguinte código demonstra um loop contendo informações sobre uma faixa de IDs de pedidos de venda. O loop começa a partir de @SalesOrderIDStart
e termina quando o valor do ID do pedido de venda é igual a @SalesOrderIDEnd
. A instrução SET
no loop while incrementa o ID do pedido de venda em 1:
DECLARE @SalesOrderIDStart INT = 43659, @SalesOrderIDEnd INT = 43759;
WHILE @SalesOrderIDStart <= @SalesOrderIDEnd
BEGIN
SELECT [AccountNumber], [CustomerID], [SalesPersonID], [TerritoryID], [BillToAddressID], [ShipToAddressID], [ShipMethodID], [CreditCardID], [CreditCardApprovalCode], [TaxAmt], [Freight], [TotalDue]
FROM [AdventureWorks2019].[Sales].[SalesOrderHeader]
WHERE SalesOrderID = @SalesOrderIDStart;
SET @SalesOrderIDStart = @SalesOrderIDStart + 1;
END;
Tratamento de Erros Usando Variáveis
O tratamento de erros é necessário para capturar erros durante a execução do código. Por exemplo, se você definiu uma chave primária na coluna ID e alguém tentar inserir um valor duplicado, isso resultaria em um erro de violação de chave primária. O código a seguir usa o bloco TRY CATCH
para capturar o erro devido à violação da chave primária. O bloco CATCH
atribui a mensagem de erro à variável @ErrorMessage
e imprime a mensagem:
CREATE TABLE Employee
(
ID int PRIMARY KEY,
First_name varchar(50),
Country varchar(20)
);
BEGIN TRY
INSERT INTO Employee Values(1, 'Rajendra', 'India');
-- Inserindo Registro Duplicado que resultará em erro devido à CHAVE PRIMÁRIA
INSERT INTO Employee Values(1, 'Rajendra', 'India');
END TRY
BEGIN CATCH
-- Declare variáveis para armazenar informações de erro
DECLARE @ErrorMessage NVARCHAR(MAX);
SELECT @ErrorMessage = ERROR_MESSAGE();
PRINT 'Ocorreu um erro: ' + @ErrorMessage;
END CATCH;
Usando variáveis, você pode lidar efetivamente com erros e fornecer mensagens de erro apropriadas.
Conclusão
A declaração de variáveis no SQL Server permite que os desenvolvedores armazenem e manipulem dados durante a execução de um script ou procedimento armazenado. As variáveis são úteis para manter conjuntos de resultados intermediários, controlar o fluxo de trabalho, preparar declarações SQL dinâmicas e tratamento de erros. Usando variáveis de forma eficaz, os desenvolvedores podem escrever um código mais eficiente e de fácil manutenção.
Artigo Atualizado em: 2023-08-16