Partilhar via

SET @local_variable (Transact-SQL)

  • Artigo

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

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

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 bit AND e atribuir
  • ^= - Bit a bit XOR e atribuir
  • |= - Bit a bit OR 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, RELATIVEe 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 STATICpalavras-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_ONLYcursor , 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 BROWSEe 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, SELECTe 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_variableo .

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;

Conteúdo relacionado