SET @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 de SQL no Microsoft Fabric
Warehouse no Microsoft Fabric
Define a variável local especificada, criada anteriormente usando a DECLARE @local_variable
instrução, para o valor especificado.
Convenções de sintaxe de Transact-SQL
Sintaxe
Sintaxe do SQL Server, do Banco de Dados SQL do Azure ou de uma Instância Gerenciada de SQL do Azure:
SET
{ @local_variable
[ . { property_name | field_name } ] = { expression | udt_name { . | :: } method_name }
}
| { @SQLCLR_local_variable.mutator_method }
| { @local_variable
{ += | -= | *= | /= | %= | &= | ^= | |= } expression
}
| { @cursor_variable =
{ @cursor_variable | cursor_name
| { CURSOR [ [ LOCAL | GLOBAL ] ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OF column_name [ , ...n ] ] } ]
}
}
}
A sintaxe do Azure Synapse Analytics, do Parallel Data Warehouse e do Microsoft Fabric:
SET @local_variable { = | += | -= | *= | /= | %= | &= | ^= | |= } expression
Observação
Para exibir a sintaxe do Transact-SQL para o SQL Server 2014 (12.x) e versões anteriores, confira a Documentação das versões anteriores.
Argumentos
@local_variable
O nome de uma variável de qualquer tipo, exceto cursor, text, ntext, image ou table. Os nomes de variável devem começar com um sinal de arroba ( @
). Os nomes de variável precisam seguir as regras para identificadores.
property_name
Uma propriedade de um tipo definido pelo usuário.
field_name
Um campo público de um tipo definido pelo usuário.
udt_name
O nome de um tipo de dados CLR (Common Language Runtime) definido pelo usuário.
{ . | :: }
Especifica um método de um tipo de dados CLR definido pelo usuário. Para um método de instância (não estático), use um ponto (.
). Para um método estático, use dois pontos (::
). Para invocar um método, uma propriedade ou um campo de um tipo de dados CLR definido pelo usuário, você deve ter a permissão EXECUTE no tipo.
method_name ( argumento [ ,... n ] )
Um método de um tipo definido pelo usuário que obtém um ou mais argumentos para modificar o estado de uma instância de um tipo. Os métodos estáticos devem ser públicos.
@SQLCLR_local_variable
Uma variável cujo tipo está localizado em um assembly. Para obter mais informações, consulte Conceitos de programação de integração CLR (Common Language Runtime).
mutator_method
Um método no assembly que pode alterar o estado do objeto. SQLMethodAttribute.IsMutator é aplicado a este método.
{ += | -= | *= | /= | %= | &= | ^= | |= }
Operador de atribuição composto:
+=
- Adicionar e atribuir-=
- Subtrair e atribuir*=
- Multiplique e atribua/=
- Dividir e atribuir%=
- Módulo e atribuição&=
- Bit a bitAND
e atribuir^=
- Bit a bitXOR
e atribuir|=
- Bit a bitOR
e atribuir
expressão
Qualquer expression válida.
cursor_variable
O nome de uma variável de cursor. Se a variável de cursor de destino tiver mencionado anteriormente um cursor diferente, essa referência anterior será removida.
cursor_name
O nome de um cursor declarado usando a DECLARE CURSOR
instrução.
CURSOR
Especifica que a SET
instrução contém uma declaração de um cursor.
SCROLL
Especifica que o cursor dá suporte a todas as opções de busca: FIRST
, LAST
, NEXT
, PRIOR
, RELATIVE
e ABSOLUTE
. Você não pode especificar SCROLL
quando também especifica FAST_FORWARD
.
FORWARD_ONLY
Especifica que o cursor suporta apenas a FETCH NEXT
opção. O cursor é recuperado somente em uma direção, da primeira para a última linha. Quando você especifica FORWARD_ONLY
sem as STATIC
palavras-chave , KEYSET
, ou DYNAMIC
, o cursor é implementado como DYNAMIC
. Se você não especificar ou FORWARD_ONLY
, é o padrão, a menos que você especifique as palavras-chave STATIC
, KEYSET
, ou DYNAMIC
. FORWARD_ONLY
SCROLL
Para STATIC
, KEYSET
, e DYNAMIC
cursores, SCROLL
é o padrão.
STATIC
Define um cursor que faz uma cópia temporária dos dados a serem usados por ele. Todas as solicitações para o cursor são respondidas a partir desta tabela temporária no tempdb
. Como resultado, as modificações feitas nas tabelas base, depois que o cursor é aberto, não são refletidas nos dados retornados pelas buscas feitas para o cursor. E esse cursor não dá suporte a modificações.
KEYSET
Especifica que a associação e a ordem de linhas no cursor são fixas, quando o cursor é aberto. O conjunto de chaves que identificam exclusivamente as linhas é incorporado ao conjunto de chaves no tempdb
. Alterações em valores não chave nas tabelas base, realizadas pelo proprietário do cursor ou confirmadas por outros usuários, são visíveis como rolagens do proprietário ao redor do cursor. As inserções feitas por outros usuários não são visíveis e não é possível fazer inserções por um cursor de servidor Transact-SQL.
Se uma linha for excluída, uma tentativa de buscar a linha retornará um @@FETCH_STATUS
de -2
. As atualizações de valores de chave externas ao cursor são similares à exclusão de uma linha antiga, seguida de uma inserção de uma nova linha. A linha com os novos valores não está visível e tenta buscar a linha com os valores antigos retorna um @@FETCH_STATUS
de -2
. Os novos valores são visíveis se a atualização ocorrer através do cursor especificando a WHERE CURRENT OF
cláusula.
DYNAMIC
Define um cursor que reflete todas as mudanças de dados feitas nas linhas no seu conjunto de resultados conforme o proprietário rola o cursor. Os valores de dados, a ordem e a associação das linhas podem ser alterados em cada busca. As opções de busca absoluta e relativa não têm suporte em cursores dinâmicos.
FAST_FORWARD
Especifica um FORWARD_ONLY
cursor , READ_ONLY
com otimizações habilitadas. FAST_FORWARD
não pode ser especificado quando SCROLL
também é especificado.
READ_ONLY
Impede que esse cursor faça atualizações. O cursor não pode ser referenciado em uma WHERE CURRENT OF
cláusula em uma UPDATE
instrução or DELETE
. Essa opção anula a funcionalidade padrão de um cursor para ser atualizado.
SCROLL LOCKS
Especifica se atualizações posicionadas ou exclusões feitas pelo cursor têm garantia de êxito. O SQL Server bloqueia as linhas à medida que são lidas no cursor para garantir a disponibilidade para modificações posteriores. Você não pode especificar SCROLL_LOCKS
quando FAST_FORWARD
também é especificado.
OPTIMISTIC
Especifica que as atualizações posicionadas e exclusões realizadas pelo cursor não terão êxito se a linha tiver sido atualizada desde que foi lida no cursor. SQL Server não bloqueia linhas à medida que são lidas no cursor. Em vez disso, ele usará comparações de valores da coluna de carimbo de data/hora ou um valor de soma de verificação, se a tabela não tiver nenhuma coluna de carimbo de data/hora, para determinar se a linha foi modificada depois de lida no cursor. Se a linha tiver sido modificada, a tentativa de atualização ou exclusão posicionada falhará. Você não pode especificar OPTIMISTIC
quando FAST_FORWARD
também é especificado.
TYPE_WARNING
Especifica que uma mensagem de aviso é enviada ao cliente quando o cursor é convertido implicitamente em outro a partir do tipo solicitado.
FOR select_statement
Uma instrução padrão SELECT
que define o conjunto de resultados do cursor. As palavras-chave FOR BROWSE
e INTO
não são permitidas no select_statement de uma declaração de cursor.
Se você usar DISTINCT
, UNION
, GROUP BY
, ou HAVING
, ou incluir uma expressão agregada no select_list, o cursor será criado como STATIC
.
Se cada tabela subjacente não tiver um índice exclusivo e um cursor ISO SCROLL
ou se um cursor Transact-SQL KEYSET
for solicitado, o cursor será automaticamente um STATIC
cursor.
Se select_statement contiver uma ORDER BY
cláusula na qual as colunas não são identificadores de linha exclusivos, um DYNAMIC
cursor será convertido em um KEYSET
cursor ou em um STATIC
cursor se um KEYSET
cursor não puder ser aberto. Esse processo também ocorre para um cursor definido usando a sintaxe ISO, mas sem a STATIC
palavra-chave.
READ ONLY
Impede que esse cursor faça atualizações. O cursor não pode ser referenciado em uma WHERE CURRENT OF
cláusula em uma UPDATE
instrução or DELETE
. Essa opção anula a funcionalidade padrão de um cursor para ser atualizado. Essa palavra-chave varia da anterior READ_ONLY
, tendo um espaço em vez de um sublinhado entre READ
e ONLY
.
ATUALIZAÇÃO [ DE column_name [ ,... n ] ]
Define colunas atualizáveis em um cursor. Se OF <column_name> [ , ...n ]
for fornecido, somente as colunas listadas permitirão modificações. Quando nenhuma lista é fornecida, todas as colunas podem ser atualizadas, a menos que o cursor seja definido como READ_ONLY
.
Comentários
Depois que uma variável é declarada, ela é inicializada como NULL
. Use a SET
instrução para atribuir um valor que não NULL
seja a uma variável declarada. A SET
instrução que atribui um valor à variável retorna um único valor. Ao inicializar várias variáveis, use uma instrução separada SET
para cada variável local.
As variáveis podem ser usadas somente em expressões, não em nomes de objeto ou palavras-chave. Para construir instruções Transact-SQL dinâmicas, use EXECUTE
.
Embora as regras de sintaxe para SET @cursor_variable
incluir as LOCAL
palavras-chave e GLOBAL
, quando você usa a SET @cursor_variable = CURSOR...
sintaxe, o cursor é criado como GLOBAL
ou LOCAL
, dependendo da configuração do padrão para a opção de banco de dados do cursor local.
As variáveis de cursor sempre são locais, mesmo se fizerem referência a um cursor global. Quando uma variável de cursor faz referência a um cursor global, o cursor tem as duas referências, uma global e uma local. Para obter mais informações, consulte Exemplo D, Usar SET com um cursor global.
Para obter mais informações, confira DECLARE CURSOR (Transact-SQL).
Você pode usar o operador de atribuição composto em qualquer lugar em que tenha uma atribuição com uma expressão no lado direito do operador, incluindo variáveis, e uma SET
instrução em um UPDATE
, SELECT
e RECEIVE
.
Não use uma variável em uma SELECT
instrução para concatenar valores (ou seja, para calcular valores agregados). Resultados de consulta inesperados podem ocorrer porque todas as SELECT
expressões na lista (incluindo atribuições) não são necessariamente executadas exatamente uma vez para cada linha de saída. Para obter mais informações, consulte KB 287515.
Permissões
Requer associação à função pública . Todos os usuários podem usar SET @local_variable
o .
Exemplos
Os exemplos de código do Transact-SQL deste artigo usa o banco de dados de exemplo AdventureWorks2022
ou AdventureWorksDW2022
, que pode ser baixado da home page Microsoft SQL Server Samples and Community Projects.
R. Imprima o valor de uma variável inicializada usando SET
O exemplo a seguir cria a variável @myVar
, coloca um valor de cadeia de caracteres na variável e imprime o valor da variável @myVar
.
DECLARE @myVar CHAR(20);
SET @myVar = 'This is a test';
SELECT @myVar;
GO
B. Usar uma variável local atribuída a um valor usando SET em uma instrução SELECT
O exemplo a seguir cria uma variável local chamada @state
e usa a variável local em uma SELECT
instrução para localizar o nome (FirstName
) e o sobrenome (LastName
) de todos os funcionários que moram no estado de Oregon
.
USE AdventureWorks2022;
GO
DECLARE @state CHAR(25);
SET @state = N'Oregon';
SELECT RTRIM(FirstName) + ' ' + RTRIM(LastName) AS Name, City
FROM HumanResources.vEmployee
WHERE StateProvinceName = @state;
GO
C. Usar uma atribuição composta para uma variável local
Os dois exemplos a seguir produzem o mesmo resultado. Cada exemplo cria uma variável local chamada @NewBalance
, multiplica-a por 10
, em seguida, exibe o novo valor da variável local em uma SELECT
instrução. O segundo exemplo usa um operador de atribuição composto.
/* Example one */
DECLARE @NewBalance INT;
SET @NewBalance = 10;
SET @NewBalance = @NewBalance * 10;
SELECT @NewBalance;
GO
/* Example Two */
DECLARE @NewBalance INT = 10;
SET @NewBalance *= 10;
SELECT @NewBalance;
GO
D. Usar SET com um cursor global
O exemplo a seguir cria uma variável local e define a variável de cursor como o nome de cursor global.
DECLARE my_cursor CURSOR GLOBAL
FOR SELECT * FROM Purchasing.ShipMethod
DECLARE @my_variable CURSOR ;
SET @my_variable = my_cursor ;
--There is a GLOBAL cursor declared(my_cursor) and a LOCAL variable
--(@my_variable) set to the my_cursor cursor.
DEALLOCATE my_cursor;
GO
--There is now only a LOCAL variable reference
--(@my_variable) to the my_cursor cursor.
E. Definir um cursor usando SET
O exemplo a seguir usa a instrução SET
para definir um cursor.
DECLARE @CursorVar CURSOR;
SET @CursorVar = CURSOR SCROLL DYNAMIC
FOR
SELECT LastName, FirstName
FROM AdventureWorks2022.HumanResources.vEmployee
WHERE LastName like 'B%';
OPEN @CursorVar;
FETCH NEXT FROM @CursorVar;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM @CursorVar
END;
CLOSE @CursorVar;
DEALLOCATE @CursorVar;
GO
F. Atribuir um valor de uma consulta
O exemplo a seguir usa uma consulta para atribuir um valor a uma variável.
USE AdventureWorks2022;
GO
DECLARE @rows INT;
SET @rows = (SELECT COUNT(*) FROM Sales.Customer);
SELECT @rows;
GO
G. Atribuir um valor a uma variável de tipo definida pelo usuário modificando uma propriedade do tipo
O exemplo a seguir define o valor para o UDT (tipo definido pelo usuário) Point
modificando o valor da propriedade X
do tipo.
DECLARE @p Point;
SET @p.X = @p.X + 1.1;
SELECT @p;
GO
Saiba mais sobre como criar o Point
UDT referenciado neste exemplo e os exemplos a seguir no artigo Criando Tipos Definidos pelo Usuário.
H. Atribua um valor a uma variável de tipo definida pelo usuário chamando um método do tipo
O exemplo a seguir define um valor para o tipo point definido pelo usuário por meio da invocação do método SetXY
do tipo.
DECLARE @p Point;
SET @p=point.SetXY(23.5, 23.5);
I. Criar uma variável para um tipo CLR e chamar um método mutador
O exemplo a seguir cria uma variável para o tipo Point
e, depois, executa um método modificador em Point
.
CREATE ASSEMBLY mytest FROM 'c:\test.dll' WITH PERMISSION_SET = SAFE
CREATE TYPE Point EXTERNAL NAME mytest.Point
GO
DECLARE @p Point = CONVERT(Point, '')
SET @p.SetXY(22, 23);
Exemplos: Azure Synapse Analytics e PDW (Analytics Platform System)
Os exemplos de código do Transact-SQL deste artigo usa o banco de dados de exemplo AdventureWorks2022
ou AdventureWorksDW2022
, que pode ser baixado da home page Microsoft SQL Server Samples and Community Projects.
J. Imprima o valor de uma variável inicializada usando SET
O exemplo a seguir cria a variável @myVar
, coloca um valor de cadeia de caracteres na variável e imprime o valor da variável @myVar
.
DECLARE @myVar CHAR(20);
SET @myVar = 'This is a test';
SELECT TOP 1 @myVar FROM sys.databases;
K. Usar uma variável local atribuída a um valor usando SET em uma instrução SELECT
O exemplo a seguir cria uma variável local chamada @dept
e usa essa variável local em uma SELECT
instrução para localizar o nome (FirstName
) e o sobrenome (LastName
) de todos os Marketing
funcionários que trabalham no departamento.
DECLARE @dept CHAR(25);
SET @dept = N'Marketing';
SELECT RTRIM(FirstName) + ' ' + RTRIM(LastName) AS Name
FROM DimEmployee
WHERE DepartmentName = @dept;
L. Usar uma atribuição composta para uma variável local
Os dois exemplos a seguir produzem o mesmo resultado. Eles criam uma variável local chamada @NewBalance
, multiplicam-na por 10
e exibem o novo valor da variável local em uma instrução SELECT
. O segundo exemplo usa um operador de atribuição composto.
/* Example one */
DECLARE @NewBalance INT;
SET @NewBalance = 10;
SET @NewBalance = @NewBalance * 10;
SELECT TOP 1 @NewBalance
FROM sys.tables;
/* Example Two */
DECLARE @NewBalance INT = 10;
SET @NewBalance *= 10;
SELECT TOP 1 @NewBalance
FROM sys.tables;
M. Atribuir um valor de uma consulta
O exemplo a seguir usa uma consulta para atribuir um valor a uma variável.
-- Uses AdventureWorks
DECLARE @rows INT;
SET @rows = (SELECT COUNT(*) FROM dbo.DimCustomer);
SELECT TOP 1 @rows FROM sys.tables;