DECLARE @local_variable (Transact-SQL)
Aplica-se a: 
SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics PDW (Analytics Platform System) Ponto de extremidade de análise do SQL Warehouse no Microsoft Fabric
As variáveis são declaradas no corpo de um lote ou procedimento com a instrução DECLARE e valores são atribuídos com uma instrução SET ou SELECT. As variáveis de cursor podem ser declaradas com essa instrução e usadas com outras instruções relacionadas ao cursor. Depois da declaração, todas as variáveis são inicializadas como NULL, a menos que um valor seja fornecido como parte da declaração.
Convenções de sintaxe de Transact-SQL
Sintaxe
A sintaxe a seguir é do SQL Server e do Banco de Dados SQL do Azure:
DECLARE
{
{ @local_variable [AS] data_type [ = value ] }
| { @cursor_variable_name CURSOR }
} [ ,...n ]
| { @table_variable_name [AS] <table_type_definition> }
<table_type_definition> ::=
TABLE ( { <column_definition> | <table_constraint> | <table_index> } } [ ,...n ] )
<column_definition> ::=
column_name { scalar_data_type | AS computed_column_expression }
[ COLLATE collation_name ]
[ [ DEFAULT constant_expression ] | IDENTITY [ (seed, increment ) ] ]
[ ROWGUIDCOL ]
[ <column_constraint> ]
[ <column_index> ]
<column_constraint> ::=
{
[ NULL | NOT NULL ]
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor
| WITH ( < index_option > [ ,...n ] )
[ ON { filegroup | "default" } ]
| [ CHECK ( logical_expression ) ] [ ,...n ]
}
<column_index> ::=
INDEX index_name [ CLUSTERED | NONCLUSTERED ]
[ WITH ( <index_option> [ ,... n ] ) ]
[ ON { partition_scheme_name (column_name )
| filegroup_name
| default
}
]
[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
<table_constraint> ::=
{
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
( column_name [ ASC | DESC ] [ ,...n ]
[ WITH FILLFACTOR = fillfactor
| WITH ( <index_option> [ ,...n ] )
| [ CHECK ( logical_expression ) ] [ ,...n ]
}
<table_index> ::=
{
{
INDEX index_name [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ]
(column_name [ ASC | DESC ] [ ,... n ] )
| INDEX index_name CLUSTERED COLUMNSTORE
| INDEX index_name [ NONCLUSTERED ] COLUMNSTORE ( column_name [ ,... n ] )
}
[ WITH ( <index_option> [ ,... n ] ) ]
[ ON { partition_scheme_name ( column_name )
| filegroup_name
| default
}
]
[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
}
<index_option> ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| STATISTICS_INCREMENTAL = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
| COMPRESSION_DELAY = { 0 | delay [ Minutes ] }
| DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
[ ON PARTITIONS ( { partition_number_expression | <range> }
[ , ...n ] ) ]
| XML_COMPRESSION = { ON | OFF }
[ ON PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ] ]
}
A sintaxe a seguir é do Azure Synapse Analytics, do Parallel Data Warehouse e do Microsoft Fabric:
DECLARE
{ { @local_variable [AS] data_type } [ = value [ COLLATE <collation_name> ] ] } [ ,...n ]
Argumentos
@local_variable
O nome de uma variável. Os nomes de variável devem começar com uma arroba (@). Os nomes de variável local devem obedecer às regras de identificadores.
data_type
Qualquer tipo de tabela CLR (Common Language Runtime) definido pelo usuário e fornecido pelo sistema, ou tipo de dados alias. Uma variável não pode ser de um tipo de dados text, ntext ou image.
Para obter mais informações sobre tipos de dados do sistema, confira Tipos de dados (Transact-SQL). Para obter mais informações sobre tipos de dados CLR definidos pelo usuário ou tipos de dados alias, confira CREATE TYPE (Transact-SQL).
= value
Atribui um valor à variável embutida. O valor pode ser uma constante ou uma expressão, mas deve corresponder ao tipo de declaração de variável ou ser convertido implicitamente nesse tipo. Para obter mais informações, confira Expressões (Transact-SQL).
@cursor_variable_name
O nome de uma variável de cursor. Os nomes de variável de cursor devem começar com uma arroba (@) e devem ser compatíveis com as regras para identificadores.
CURSOR
Especifica que a variável é uma variável de cursor local.
@table_variable_name
O nome de uma variável do tipo table. Os nomes de variável devem começar com uma arroba (@) e devem estar em conformidade com as regras para identificadores.
<table_type_definition>
Define o tipo de dados table. A declaração de tabela inclui definições de coluna, nomes, tipos de dados e restrições. Os únicos tipos de restrição permitidos são PRIMARY KEY, UNIQUE, NULL e CHECK. Um tipo de dados alias não pode ser usado como um tipo de dados escalar de coluna se uma regra ou definição padrão for associada ao tipo.
<table_type_definition>
Um subconjunto de informações usado para definir uma tabela em CREATE TABLE. Elementos e definições essenciais são incluídos aqui. Para obter mais informações, veja CREATE TABLE (Transact-SQL).
n
Um espaço reservado que indica que várias variáveis podem ser especificadas e receber a atribuição de valores. Ao declarar variáveis table, a variável table deve ser a única declarada na instrução DECLARE.
column_name
O nome da coluna na tabela.
scalar_data_type
Especifica que a coluna é um tipo de dados escalar.
computed_column_expression
Uma expressão que define o valor de uma coluna computada. Essa coluna é computada a partir de uma expressão que usa outras colunas na mesma tabela. Por exemplo, uma coluna computada pode ter uma definição: cost AS price * qty. A expressão pode ser o nome de uma coluna não computada, constante, função, variável ou qualquer combinação dessas, conectada por um ou mais operadores. A expressão não pode ser uma subconsulta nem uma função definida pelo usuário. A expressão não pode fazer referência a um tipo de dado CLR definido pelo usuário.
[ COLLATE collation_name ]
Especifica a ordenação da coluna. collation_name pode ser um nome de agrupamento do Windows ou um nome de agrupamento SQL e é aplicável somente a colunas dos tipos de dados char, varchar, text, nchar, nvarchar e ntext. Se não for especificado, à coluna será atribuída a ordenação do tipo de dados definido pelo usuário (se a coluna for de um tipo de dados definido pelo usuário) ou a ordenação do banco de dados atual.
Para obter mais informações sobre os nomes de ordenação do Windows e do SQL, consulte COLLATE (Transact-SQL).
DEFAULT
Especifica o valor fornecido para a coluna quando um valor não for fornecido explicitamente durante uma inserção. As definições de DEFAULT podem ser aplicadas a qualquer coluna, com exceção das definidas como timestamp ou das colunas com a propriedade IDENTITY. As definições DEFAULT serão removidas quando a tabela for descartada. Somente um valor constante, como uma cadeia de caracteres, uma função de sistema, como SYSTEM_USER() ou NULL pode ser usado como padrão. Para manter a compatibilidade com versões anteriores do SQL Server, um nome de restrição pode ser atribuído a um DEFAULT.
constant_expression
Uma constante, um NULL ou uma função do sistema usada como valor padrão para a coluna.
IDENTITY
Indica que a nova coluna é uma coluna de identidade. Quando uma nova linha é adicionada à tabela, o SQL Server fornece um valor incremental exclusivo para a coluna. Geralmente, as colunas de identidade são usadas com restrições PRIMARY KEY para servir como o identificador exclusivo de linha para a tabela. A propriedade IDENTITY pode ser atribuída às colunas tinyint, smallint, int, decimal(p,0) ou numeric(p,0) . Apenas uma coluna de identidade pode ser criada por tabela. Padrões associados e restrições DEFAULT não podem ser usados com uma coluna de identidade. Você deve especificar a semente e o incremento, ou nenhum dos dois. Se nenhum for especificado, o padrão será (1,1).
seed
O valor usado para a primeira linha carregada na tabela.
increment
O valor incremental adicionado ao valor de identidade da linha anterior que foi carregada.
ROWGUIDCOL
Indica que a nova coluna é uma coluna de identificador exclusivo global de linha. Somente uma coluna uniqueidentifier por tabela pode ser designada como a coluna ROWGUIDCOL. A propriedade ROWGUIDCOL pode ser atribuída somente a uma coluna uniqueidentifier.
NULL | NOT NULL
Indica se será permitido um valor nulo na variável. O padrão é NULL.
PRIMARY KEY
Uma restrição que impõe integridade de entidade para uma coluna ou colunas especificadas por meio de um índice exclusivo. Somente uma restrição PRIMARY KEY pode ser criada por tabela.
UNIQUE
Uma restrição que fornece integridade de entidade para uma coluna ou colunas especificadas por meio de um índice exclusivo. Uma tabela pode ter várias restrições UNIQUE.
CLUSTERED | NONCLUSTERED
Indica que um índice clusterizado ou não clusterizado será criado para a restrição PRIMARY KEY ou UNIQUE. As restrições PRIMARY KEY usam CLUSTERED e as restrições UNIQUE usam NONCLUSTERED.
CLUSTERED só pode ser especificado para uma restrição. Se CLUSTERED for especificado para uma restrição UNIQUE e uma restrição PRIMARY KEY também for especificada, PRIMARY KEY usará NONCLUSTERED.
CHECK
Uma restrição que impõe integridade de domínio limitando os possíveis valores que podem ser inseridos em uma ou mais colunas.
logical_expression
Uma expressão lógica que retorna TRUE ou FALSE.
<index_option>
Especifica uma ou mais opções de índice. Índices não podem ser criados explicitamente em variáveis table e nenhuma estatística é mantida em variáveis table. Começando com o SQL Server 2014 (12.x), uma nova sintaxe foi introduzida, a qual permite que você crie determinados tipos de índice em linha com a definição da tabela. Usando essa nova sintaxe, você pode criar índices em variáveis de tabela como parte da definição de tabela. Em alguns casos, o desempenho pode melhorar com o uso de tabelas temporárias em vez disso, que fornecem suporte de índice completo e estatísticas.
Para obter uma descrição completa dessas opções, confira CREATE TABLE.
Variáveis de tabela e estimativas de linha
As variáveis Table não têm estatísticas de distribuição. Em muitos casos, o otimizador criará um plano de consulta supondo que a variável de tabela tem uma ou nenhuma linha. Para obter mais informações, examine Tipo de dados da tabela – Limitações e restrições.
Por esse motivo, tenha cuidado ao usar uma variável de tabela se espera um grande número de linhas (cima de 100). Considere as seguintes alternativas:
- As tabelas temporárias podem ser uma solução melhor do que as variáveis de tabela quando é possível que a contagem de linhas seja superior (maior que 100).
- Para consultas que unem a variável de tabela com outras tabelas, use a dica RECOMPILE, que levará o otimizador a usar a cardinalidade correta para a variável de tabela.
- No Banco de Dados SQL do Azure e do SQL Server 2019 (15.x) em diante, o recurso de compilação adiada de variável de tabela propagará estimativas de cardinalidade baseadas em contagens de linhas de variáveis de tabela reais, fornecendo uma contagem de linhas mais precisa para otimizar o plano de execução. Para saber mais, confira Processamento de consultas inteligente em bancos de dados SQL.
Comentários
As variáveis geralmente são usadas em um lote ou procedimento como contadores para WHILE, LOOP ou para um bloco IF...ELSE.
As variáveis podem ser usadas somente em expressões, não no lugar de nomes de objeto ou palavras-chave. Para construir instruções SQL dinâmicas, use EXECUTE.
O escopo de uma variável local é o lote no qual ela é declarada.
Uma variável de tabela não é necessariamente residente em memória. Sob demanda de memória, as páginas que pertencem a uma variável de tabela podem ser enviadas para fora do tempdb.
Você pode definir um índice embutido em uma variável de tabela.
Uma variável de cursor que atualmente tem um cursor atribuído pode ser mencionada como uma fonte em uma:
- Instrução CLOSE
- Instrução DEALLOCATE
- Instrução FETCH
- Instrução OPEN
- Instrução DELETE ou UPDATE posicionada
- Instrução de variável SET CURSOR (à direita)
Em todas essas instruções, o SQL Server gera um erro se uma variável de cursor mencionada existe, mas não tem um cursor alocado no momento. Se uma variável de cursor mencionada não existir, o SQL Server gerará o mesmo erro gerado para uma variável não declarada de outro tipo.
Uma variável de cursor:
Pode ser o destino de um tipo de cursor ou de outra variável de cursor. Para obter mais informações, consulte SET @local_variable (Transact-SQL).
Pode ser mencionada como o destino de um parâmetro de cursor de saída em uma instrução EXECUTE se a variável não tem um cursor atribuído no momento.
Deve ser considerada como um ponteiro para o cursor.
Exemplos
a. Usando DECLARE
O exemplo a seguir usa uma variável local chamada @find para recuperar informações de contato para todos os sobrenomes que começam com Man.
USE AdventureWorks2022;
GO
DECLARE @find VARCHAR(30);
/* Also allowed:
DECLARE @find VARCHAR(30) = 'Man%';
*/
SET @find = 'Man%';
SELECT p.LastName, p.FirstName, ph.PhoneNumber
FROM Person.Person AS p
JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE @find;
Veja a seguir o conjunto de resultados.
LastName FirstName Phone
------------------- ----------------------- -------------------------
Manchepalli Ajay 1 (11) 500 555-0174
Manek Parul 1 (11) 500 555-0146
Manzanares Tomas 1 (11) 500 555-0178
(3 row(s) affected)
B. Usando DECLARE com duas variáveis
O exemplo a seguir recupera os nomes de representantes de vendas do Ciclos da Adventure Works localizados no território de vendas norte-americano que venderam pelo menos US$ 2.000.000 durante o ano.
USE AdventureWorks2022;
GO
SET NOCOUNT ON;
GO
DECLARE @Group nvarchar(50), @Sales MONEY;
SET @Group = N'North America';
SET @Sales = 2000000;
SET NOCOUNT OFF;
SELECT FirstName, LastName, SalesYTD
FROM Sales.vSalesPerson
WHERE TerritoryGroup = @Group and SalesYTD >= @Sales;
C. Declarando uma variável de tabela de tipos
O exemplo a seguir cria uma variável table que armazena os valores especificados na cláusula OUTPUT da instrução UPDATE. Seguem duas instruções SELECT que retornam os valores em @MyTableVar e os resultados da operação de atualização na tabela Employee. Os resultados na coluna INSERTED.ModifiedDate são diferentes dos valores da coluna ModifiedDate na tabela Employee. Isso porque o gatilho AFTER UPDATE, que atualiza o valor de ModifiedDate com a data atual, está definido na tabela Employee. Porém, as colunas retornadas de OUTPUT refletem os dados antes de os gatilhos serem disparados. Para obter mais informações, confira Cláusula OUTPUT (Transact-SQL).
USE AdventureWorks2022;
GO
DECLARE @MyTableVar TABLE (
EmpID INT NOT NULL,
OldVacationHours INT,
NewVacationHours INT,
ModifiedDate DATETIME);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
OUTPUT INSERTED.BusinessEntityID,
DELETED.VacationHours,
INSERTED.VacationHours,
INSERTED.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
--Note that ModifiedDate reflects the value generated by an
--AFTER UPDATE trigger.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO
D. Declarando uma variável de tabela de tipos, com índices embutidos
O exemplo a seguir cria uma variável table com um índice embutido clusterizado e dois índices embutidos não clusterizados.
DECLARE @MyTableVar TABLE (
EmpID INT NOT NULL,
PRIMARY KEY CLUSTERED (EmpID),
UNIQUE NONCLUSTERED (EmpID),
INDEX CustomNonClusteredIndex NONCLUSTERED (EmpID)
);
GO
A consulta a seguir retorna informações sobre os índices criados na consulta anterior.
SELECT *
FROM tempdb.sys.indexes
WHERE object_id < 0;
GO
E. Declarando uma variável de tipo de tabela definido pelo usuário
O exemplo a seguir cria um parâmetro com valor de tabela ou uma variável de tabela chamada @LocationTVP. Isso requer um tipo de tabela definido pelo usuário correspondente chamado LocationTableType. Para obter mais informações sobre como criar um tipo de tabela definido pelo usuário, confira CREATE TYPE (Transact-SQL). Para obter mais informações sobre parâmetros com valor de tabela, confira Usar parâmetros com valor de tabela (Mecanismo de Banco de Dados).
DECLARE @LocationTVP
AS LocationTableType;
Exemplos: Azure Synapse Analytics e PDW (Analytics Platform System)
F. Usando DECLARE
O exemplo a seguir usa uma variável local chamada @find para recuperar informações de contato para todos os sobrenomes que começam com Walt.
-- Uses AdventureWorks
DECLARE @find VARCHAR(30);
/* Also allowed:
DECLARE @find VARCHAR(30) = 'Man%';
*/
SET @find = 'Walt%';
SELECT LastName, FirstName, Phone
FROM DimEmployee
WHERE LastName LIKE @find;
G. Usando DECLARE com duas variáveis
O exemplo a seguir recupera e usa variáveis para especificar os nomes e sobrenomes de funcionários na tabela DimEmployee.
-- Uses AdventureWorks
DECLARE @lastName VARCHAR(30), @firstName VARCHAR(30);
SET @lastName = 'Walt%';
SET @firstName = 'Bryan';
SELECT LastName, FirstName, Phone
FROM DimEmployee
WHERE LastName LIKE @lastName AND FirstName LIKE @firstName;