SET @local_variable (Transact-SQL)

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Définit la variable locale spécifiée, créée précédemment à l’aide de l’instruction DECLARE @local_variable , sur la valeur spécifiée.

Conventions de la syntaxe Transact-SQL

Syntaxe

Syntaxe pour SQL Server, Azure SQL Database et Azure SQL Managed Instance :

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 ] ] } ]
      }
    }
}

Syntaxe pour Azure Synapse Analytics, Parallel Data Warehouse et Microsoft Fabric :

SET @local_variable { = | += | -= | *= | /= | %= | &= | ^= | |= } expression

Arguments

@local_variable

Nom d’une variable de tout type sauf cursor, text, ntext, image ou table. Les noms de variables doivent commencer par une arobase ( @ ). Les noms de variable doivent respecter les règles applicables aux identificateurs.

property_name

Propriété d’un type défini par l’utilisateur.

field_name

Champ public d’un type défini par l’utilisateur.

udt_name

Nom d’un type CLR (Common Language Runtime) défini par l’utilisateur.

{ . | :: }

Définit la méthode d'un type CLR défini par l'utilisateur. Pour une méthode d’instance (non statique), utilisez une période (.). Pour une méthode statique, utilisez deux points-virgules (::). Pour appeler une méthode, une propriété ou un champ de type CLR défini par l'utilisateur, vous devez avoir l'autorisation EXECUTE sur le type.

method_name ( argument [ ,... n ] )

Méthode d’un type défini par l’utilisateur qui utilise un ou plusieurs arguments pour modifier l’état d’une instance d’un type. Les méthodes statiques doivent être publiques.

@SQLCLR_local_variable

Variable dont le type se trouve dans un assembly. Pour plus d’informations, consultez les concepts de programmation d’intégration clR (Common Language Runtime).

mutator_method

Méthode de l’assembly qui peut modifier l’état de l’objet. SQLMethodAttribute.IsMutator est appliqué à cette méthode.

{ += | -= | *= | /= | %= | &= | ^= | |= }

Opérateur d'assignation composé :

• += - Ajouter et affecter
• -= - Soustraire et affecter
• *= - Multiplier et affecter
• /= - Diviser et affecter
• %= - Modulo et assigner
• &= - Au niveau AND du bit et assigner
• ^= - Au niveau XOR du bit et assigner
• |= - Au niveau OR du bit et assigner

expression

Toute expression valide.

cursor_variable

Nom d'une variable curseur. Si la variable curseur cible référençait un autre curseur, cette ancienne référence est supprimée.

cursor_name

Nom d’un curseur déclaré à l’aide de l’instruction DECLARE CURSOR .

CURSOR

Spécifie que l’instruction SET contient une déclaration d’un curseur.

SCROLL

Spécifie que le curseur prend en charge toutes les options de récupération : FIRST, , LASTNEXT, PRIOR, , RELATIVEet ABSOLUTE. Vous ne pouvez pas spécifier SCROLL quand vous spécifiez FAST_FORWARDégalement .

FORWARD_ONLY

Spécifie que le curseur prend uniquement en charge l’option FETCH NEXT . Le curseur est récupéré dans une seule direction, de la première à la dernière ligne. Lorsque vous spécifiez FORWARD_ONLY sans les mots clés ou DYNAMIC les STATICmots KEYSETclés, le curseur est implémenté en tant que DYNAMIC. Si vous ne spécifiez pas l’un FORWARD_ONLY ou SCROLLl’autre , FORWARD_ONLY est la valeur par défaut, sauf si vous spécifiez les mots clés STATIC, KEYSETou DYNAMIC. Pour STATIC, KEYSETet DYNAMIC les curseurs, SCROLL est la valeur par défaut.

STATIC

Définit un curseur qui effectue une copie temporaire des données qu'il doit utiliser. Toutes les demandes adressées au curseur sont répondues à partir de cette table temporaire dans tempdb. Par conséquent, les modifications apportées aux tables de base après l’ouverture du curseur ne sont pas répercutées dans les données renvoyées par les extractions effectuées sur le curseur. Et ce curseur ne prend pas en charge les modifications.

KEYSET

Spécifie que l'appartenance au curseur et l'ordre des lignes sont fixés lors de l'ouverture du curseur. L’ensemble de clés qui identifient de manière unique les lignes est intégré à la table de clés dans tempdb. Les modifications apportées aux valeurs non-clé dans les tables de base, par le propriétaire du curseur ou validées par d'autres utilisateurs, sont visibles lorsque le propriétaire du curseur déplace le curseur. Les insertions effectuées par d’autres utilisateurs ne sont pas visibles et les insertions ne peuvent pas être effectuées via un curseur côté serveur Transact-SQL.

Si une ligne est supprimée, une tentative d’extraction de la ligne retourne une @@FETCH_STATUS valeur .-2 Les mises à jour de valeurs clés effectuées hors du curseur sont semblables à la suppression de l'ancienne ligne suivie de l'insertion d'une nouvelle. La ligne avec les nouvelles valeurs n’est pas visible et tente d’extraire la ligne avec les anciennes valeurs retournent une valeur @@FETCH_STATUS -2. Les nouvelles valeurs sont visibles si la mise à jour se produit via le curseur en spécifiant la WHERE CURRENT OF clause.

DYNAMIC

Définit un curseur qui reflète toutes les modifications des lignes dans son jeu de résultats lorsque le propriétaire du curseur déplace le curseur. Les valeurs des données, l'ordre et l'appartenance aux lignes peuvent changer à chaque extraction. Les curseurs dynamiques ne prennent pas en charge les options d’extraction ABSOLUTE et RELATIVE.

FAST_FORWARD

Spécifie un FORWARD_ONLYcurseur , READ_ONLY avec optimisations activées. FAST_FORWARD ne peut pas être spécifié quand SCROLL il est également spécifié.

READ_ONLY

Empêche les mises à jour par l'intermédiaire de ce curseur. Le curseur ne peut pas être référencé dans une WHERE CURRENT OF clause d’une instruction ou DELETE d’une UPDATE instruction. Cette option remplace la possibilité par défaut de mise à jour d'un curseur.

SCROLL LOCKS

Spécifie que la réussite des mises à jour ou des suppressions positionnées effectuées via le curseur est garantie. SQL Server verrouille les lignes lorsqu’elles sont lues dans le curseur pour garantir leur disponibilité lors des modifications ultérieures. Vous ne pouvez pas spécifier SCROLL_LOCKS quand FAST_FORWARD est également spécifié.

OPTIMISTIC

Spécifie que les mises à jour ou les suppressions positionnées effectuées avec le curseur échouent si la ligne a été mise à jour depuis qu’elle a été lue dans le curseur. SQL Server ne verrouille pas les lignes lorsqu’elles sont lues dans le curseur. En revanche, il utilise des comparaisons des valeurs de la colonne timestamp, ou une valeur de somme de contrôle si la table n’a pas de colonne timestamp, pour déterminer si la ligne a été modifiée après avoir été lue dans le curseur. Si la ligne a été modifiée, la mise à jour ou la suppression positionnée que vous avez tentée échoue. Vous ne pouvez pas spécifier OPTIMISTIC quand FAST_FORWARD est également spécifié.

TYPE_WARNING

Indique qu'un message d'avertissement est envoyé au client lorsque le curseur est converti implicitement du type demandé vers un autre type.

FOR select_statement

Instruction standard SELECT qui définit le jeu de résultats du curseur. Les mots clés FOR BROWSEet INTO ne sont pas autorisés dans la select_statement d’une déclaration de curseur.

Si vous utilisez DISTINCT, ou GROUP BYUNIONHAVINGsi vous incluez une expression d’agrégation dans l’select_list, le curseur est créé en tant que STATIC.

Si chaque table sous-jacente n’a pas d’index unique et un curseur ISO SCROLL ou si un curseur Transact-SQL KEYSET est demandé, le curseur est automatiquement un STATIC curseur.

Si select_statement contient une ORDER BY clause dans laquelle les colonnes ne sont pas des identificateurs de lignes uniques, un DYNAMIC curseur est converti en KEYSET curseur ou en curseur STATIC si un KEYSET curseur ne peut pas être ouvert. Ce processus se produit également pour un curseur défini à l’aide de la syntaxe ISO, mais sans le STATIC mot clé.

READ ONLY

Empêche les mises à jour par l'intermédiaire de ce curseur. Le curseur ne peut pas être référencé dans une WHERE CURRENT OF clause d’une instruction ou DELETE d’une UPDATE instruction. Cette option remplace la possibilité par défaut de mise à jour d'un curseur. Ce mot clé varie selon le précédent READ_ONLY en ayant un espace au lieu d’un trait de soulignement entre READ et ONLY.

UPDATE [ OF column_name [ ,... n ] ]

Définit les colonnes qui peuvent être mises à jour par le curseur. Si OF <column_name> [ , ...n ] est fourni, seules les colonnes listées permettent les modifications. Lorsqu’aucune liste n’est fournie, toutes les colonnes peuvent être mises à jour, sauf si le curseur est défini comme READ_ONLY.

Notes

Une fois qu’une variable est déclarée, elle est initialisée sur NULL. Utilisez l’instruction SET pour affecter une valeur qui n’est pas NULL à une variable déclarée. L’instruction SET qui affecte une valeur à la variable retourne une valeur unique. Lorsque vous initialisez plusieurs variables, utilisez une instruction distincte SET pour chaque variable locale.

Vous ne pouvez utiliser les variables que dans les expressions, pas dans les noms d’objet ou les mots clés. Pour construire des instructions Transact-SQL dynamiques, utilisez EXECUTE.

Bien que les règles de syntaxe pour SET @cursor_variable inclure les mots clés et GLOBAL les LOCAL mots clés, lorsque vous utilisez la SET @cursor_variable = CURSOR... syntaxe, le curseur est créé en tant que GLOBAL ou LOCAL, selon le paramètre de l’option de base de données de curseur locale par défaut.

Les variables de curseurs sont toujours locales, même lorsqu'elles font référence à un curseur global. Dans ce cas, le curseur comporte à la fois une référence de curseur global et de curseur local. Pour plus d’informations, consultez l’exemple D, utiliser SET avec un curseur global.

Pour plus d’informations, consultez DECLARE CURSOR (Transact-SQL).

Vous pouvez utiliser l’opérateur d’affectation composée n’importe où où vous avez une affectation avec une expression sur le côté droit de l’opérateur, y compris les variables et une dans une UPDATEinstruction , SELECTet RECEIVE une SET instruction.

N’utilisez pas de variable dans une SELECT instruction pour concaténer des valeurs (autrement dit, pour calculer des valeurs d’agrégation). Des résultats de requête inattendus peuvent se produire, car toutes les expressions de la SELECT liste (y compris les affectations) ne sont pas nécessairement exécutées une seule fois pour chaque ligne de sortie. Pour plus d’informations, consultez la base de connaissances 287515.

autorisations

Nécessite l'appartenance au rôle public . Tous les utilisateurs peuvent utiliser SET @local_variable.

Exemples

Les exemples de code Transact-SQL de cet article sont fondés sur l’échantillon de base de données AdventureWorks2022 ou AdventureWorksDW2022 fourni, que vous pouvez télécharger à partir de la page d’accueil Échantillons et projets communautaires Microsoft SQL Server.

A. Imprimer la valeur d’une variable initialisée à l’aide de SET

L’exemple suivant crée la variable @myVar, place une valeur de chaîne dans la variable et imprime la valeur de la variable @myVar.

DECLARE @myVar CHAR(20);
SET @myVar = 'This is a test';
SELECT @myVar;
GO

B. Utiliser une variable locale affectée à une valeur à l’aide de SET dans une instruction SELECT

L’exemple suivant crée une variable locale nommée @state et utilise la variable locale dans une SELECT instruction pour rechercher le prénom (FirstName) et le nom de famille (LastName) de tous les employés qui vivent dans l’état 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. Utiliser une affectation composée pour une variable locale

Les deux exemples suivants produisent le même résultat. Chaque exemple crée une variable locale nommée @NewBalance, la multiplie par 10, puis affiche la nouvelle valeur de la variable locale dans une SELECT instruction. Le deuxième exemple utilise un opérateur d'assignation composée.

/* 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. Utiliser SET avec un curseur global

L'exemple suivant crée une variable locale et affecte le nom de curseur global à la variable curseur.

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. Définir un curseur à l’aide de SET

L'exemple suivant utilise l'instruction SET pour définir un curseur.

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. Affecter une valeur à partir d’une requête

L'exemple suivant utilise une requête pour affecter une valeur à une variable.

USE AdventureWorks2022;
GO
DECLARE @rows INT;
SET @rows = (SELECT COUNT(*) FROM Sales.Customer);
SELECT @rows;
GO

G. Affecter une valeur à une variable de type définie par l’utilisateur en modifiant une propriété du type

L'exemple suivant définit une valeur pour le type Point défini par l'utilisateur en modifiant la valeur de la propriété X du type.

DECLARE @p Point;
SET @p.X = @p.X + 1.1;
SELECT @p;
GO

En savoir plus sur la création d’un Point UDT référencé dans cet exemple et les exemples suivants dans l’article Création de types définis par l’utilisateur (UDT).

H. Affecter une valeur à une variable de type définie par l’utilisateur en appelant une méthode du type

L’exemple suivant définit une valeur pour le type point défini par l’utilisateur en appelant la méthode SetXY du type.

DECLARE @p Point;
SET @p=point.SetXY(23.5, 23.5);

I. Créer une variable pour un type CLR et appeler une méthode mutator

L'exemple suivant crée une variable pour le type Point, puis exécute une méthode mutateur dans 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);

Exemples : Azure Synapse Analytics et Analytics Platform System (PDW)

Les exemples de code Transact-SQL de cet article sont fondés sur l’échantillon de base de données AdventureWorks2022 ou AdventureWorksDW2022 fourni, que vous pouvez télécharger à partir de la page d’accueil Échantillons et projets communautaires Microsoft SQL Server.

J. Imprimer la valeur d’une variable initialisée à l’aide de SET

L’exemple suivant crée la variable @myVar, place une valeur de chaîne dans la variable et imprime la valeur de la variable @myVar.

DECLARE @myVar CHAR(20);
SET @myVar = 'This is a test';
SELECT TOP 1 @myVar FROM sys.databases;

K. Utiliser une variable locale affectée à une valeur à l’aide de SET dans une instruction SELECT

L’exemple suivant crée une variable locale nommée @dept et utilise cette variable locale dans une SELECT instruction pour rechercher le prénom (FirstName) et le nom de famille (LastName) de tous les employés qui travaillent dans le Marketing service.

DECLARE @dept CHAR(25);
SET @dept = N'Marketing';
SELECT RTRIM(FirstName) + ' ' + RTRIM(LastName) AS Name
FROM DimEmployee
WHERE DepartmentName = @dept;

L. Utiliser une affectation composée pour une variable locale

Les deux exemples suivants produisent le même résultat. Ils créent une variable locale nommée @NewBalance, la multiplient par 10 et affichent la nouvelle valeur de la variable locale dans une instruction SELECT. Le deuxième exemple utilise un opérateur d'assignation composée.

/* 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. Affecter une valeur à partir d’une requête

L'exemple suivant utilise une requête pour affecter une valeur à une variable.

-- Uses AdventureWorks

DECLARE @rows INT;
SET @rows = (SELECT COUNT(*) FROM dbo.DimCustomer);
SELECT TOP 1 @rows FROM sys.tables;

Contenu connexe

• Opérateurs composés (Transact-SQL)
• DECLARE @local_variable (Transact-SQL)
• EXECUTE (Transact-SQL)
• SELECT (Transact-SQL)
• Instructions SET (Transact-SQL)