SET @local_variable (Transact-SQL)

Article
12/15/2008

Affecte la valeur spécifiée à la variable locale spécifiée, précédemment créée en utilisant l'instruction DECLARE **@**local_variable.

Icône Lien de rubrique Conventions de la syntaxe de Transact-SQL

Syntaxe

SET 
{ @local_variable 
    [:: property_name | field_name ] = expression | udt_name { . | :: } method_name(argument [ ,...n ] ) 
}
| 
{ @cursor_variable = 
    { @cursor_variable | cursor_name 
    | { CURSOR [ 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 ] ] } ] 
      } 
    }
}

Arguments

**@**local_variable

Nom d'une variable de n'importe quel type sauf cursor, text, ntext, image ou table. Les noms de variables doivent commencer par le signe @. Les noms de variables doivent être conformes aux règles des 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 défini par l'utilisateur CLR (Common Language Runtime).

{ . | :: }

Définit la méthode d'un type CLR défini par l'utilisateur. Pour une méthode d'instance (non statique), utilisez un point (.). Pour une méthode statique, utilisez deux fois le deux-points (::). 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 de 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.

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 de curseur.

SCROLL

Indique que le curseur prend en charge toutes les option d'extraction : FIRST, LAST, NEXT, PRIOR, RELATIVE et ABSOLUTE. SCROLL ne peut pas être spécifié lorsque FAST_FORWARD est également spécifié.

FORWARD_ONLY

Spécifie que le curseur gère uniquement l'option FETCH NEXT. Le curseur peut être extrait dans une seule direction, de la première à la dernière ligne. Lorsque FORWARD_ONLY est spécifié sans le mot clé STATIC, KEYSET ou DYNAMIC, le curseur est implémenté sous la forme DYNAMIC. Si vous ne spécifiez ni FORWARD_ONLY ni SCROLL, FORWARD_ONLY est choisi par défaut, sauf si les mots clés STATIC, KEYSET ou DYNAMIC sont spécifiés. Pour les curseurs STATIC, KEYSET et DYNAMIC, SCROLL est la valeur par défaut.

Remarque :
Dans SQL Server 2000, les options de curseur FAST_FORWARD et FORWARD_ONLY sont mutuellement exclusives. Si l'une de ces options est spécifiée, l'autre ne peut pas l'être, et une erreur est générée. Dans SQL Server 2005, les deux mots clés peuvent être utilisés dans la même instruction DECLARE CURSOR.

STATIC

Définit un curseur qui fait une copie temporaire des données qu'il doit utiliser. Toutes les demandes envoyées au curseur reçoivent une réponse à partir de cette table temporaire dans tempdb ; par conséquent, les modifications effectuées dans les tables de base ne sont pas reflétées dans les données renvoyées par les extractions faites dans le curseur ; ce curseur n'autorise pas les modifications.

KEYSET

Spécifie que l'appartenance au curseur et l'ordre des lignes sont fixés lors de l'ouverture du curseur. Le groupe de clés qui identifie de manière unique les lignes est intégré à la table keyset 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 de serveur Transact-SQL.

Si une ligne est supprimée, une tentative d'extraction de la ligne renvoie un @@FETCH_STATUS égal à -2. Les mises à jour des valeurs de clé en dehors du curseur reviennent à supprimer l'ancienne ligne, puis à insérer la nouvelle ligne. La ligne avec les nouvelles valeurs n'est pas visible et les tentatives d'extraction avec les anciennes valeurs renvoient un @@FETCH_STATUS égal à -2. Les nouvelles valeurs sont visibles si la mise à jour est effectuée via le curseur en spécifiant la clause WHERE CURRENT OF.

DYNAMIC

Définit un curseur qui reflète toutes les modifications des lignes dans son ensemble 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 curseur FORWARD_ONLY, READ_ONLY pour lequel les optimisations sont activées. FAST_FORWARD ne peut pas être spécifié lorsque SCROLL est spécifié.

Remarque :
Dans SQL Server 2000, les options de curseur FAST_FORWARD et FORWARD_ONLY sont mutuellement exclusives. Si l'une de ces options est spécifiée, l'autre ne peut pas l'être, et une erreur est générée. Dans SQL Server 2005, les deux mots clés peuvent être utilisés dans la même instruction DECLARE CURSOR.

READ_ONLY

SCROLL LOCKS

Indique que les mises à jour ou des suppressions positionnées effectuées via le curseur sont assurées d'aboutir. SQL Server verrouille les lignes lors de leur lecture dans le curseur pour garantir leur disponibilité pour les modifications ultérieures. SCROLL_LOCKS ne peut pas être spécifié lorsque FAST_FORWARD est spécifié.

OPTIMISTIC

Indique que les mises à jour ou les suppressions positionnées effectuées via le curseur échouent si la ligne a été mise à jour depuis sa lecture dans le curseur. SQL Server ne verrouille pas les lignes lorsqu'elles sont lues dans le curseur. Il utilise à la place 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. OPTIMISTIC ne peut pas être spécifié lorsque FAST_FORWARD est 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 SELECT standard qui définit l'ensemble de résultats du curseur. Les mots clés COMPUTE, COMPUTE BY, FOR BROWSE et INTO ne sont pas autorisés dans l'instruction de sélection select_statement d'une déclaration de curseur.

Si DISTINCT, UNION, GROUP BY ou HAVING est utilisé, ou qu'une expression d'agrégation est incluse dans la liste de sélection select_list, le curseur créé est STATIC.

Si chaque table sous-jacente ne dispose pas d'un index unique et qu'un curseur SQL-92 SCROLL ou Transact-SQL KEYSET est demandé, le curseur est automatiquement STATIC.

Si l'instruction select_statement contient une clause ORDER BY dans laquelle les colonnes ne sont pas des identificateurs de lignes uniques, un curseur DYNAMIC est converti en curseur KEYSET ou en curseur STATIC si un curseur KEYSET ne peut pas être ouvert. Ceci se produit également pour un curseur défini en utilisant la syntaxe SQL-92 sans le mot clé STATIC.

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 clause WHERE CURRENT OF dans une instruction UPDATE ou DELETE. Cette option remplace la possibilité par défaut de mise à jour d'un curseur. Ce mot clé est désormais représenté par les mots READ et ONLY séparés par un espace, au lieu d'un trait de soulignement (READ_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 indiquées permettent les modifications. Si aucune liste n'est spécifiée, toutes les colonnes peuvent être mises à jour, sauf si le curseur a été défini en tant que curseur READ_ONLY.

Notes

Une fois une variable déclarée, la variable est initialisée avec la valeur NULL. Utilisez l'instruction SET pour affecter une valeur autre que NULL à une variable déclarée. L'instruction SET qui affecte une valeur à la variable renvoie une seule valeur. Lorsque vous initialisez plusieurs variables, utilisez une instruction SET distincte pour chaque variable locale.

Les variables peuvent être utilisées uniquement dans les expressions et pas dans les noms d'objets ni les mots clés. Pour créer des instructions dynamiques Transact-SQL, utilisez EXECUTE.

Les règles de syntaxe de SET **@**cursor_variable n'incluent pas les mots clés LOCAL et GLOBAL. Lorsque la syntaxe SET **@**cursor_variable = CURSOR... est utilisée, le curseur GLOBAL ou LOCAL est créé, selon la valeur de l'option de base de données default to local cursor.

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 C.

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

Autorisations

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

Exemples

A. Impression de la valeur d'une variable initialisée avec 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. Utilisation d'une variable locale ayant reçu une valeur avec SET dans une instruction SELECT

L'exemple suivant crée la variable locale @state et utilise cette variable locale dans une instruction SELECT pour rechercher le nom et le prénom de tous les employés qui résident dans l'État de l'Oregon.

USE AdventureWorks;
GO
DECLARE @state char(25);
SET @state = N'Oregon';
SELECT RTRIM(FirstName) + ' ' + RTRIM(LastName) AS Name, City
FROM HumanResources.vEmployee
WHERE StateProvinceName = @state;

C. Utilisation de 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; 
--There is now only a LOCAL variable reference
--(@my_variable) to the my_cursor cursor.

D. Définition d'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 AdventureWorks.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;

E. Affectation d'une valeur depuis une requête

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

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

F. Affectation d'une valeur à une variable 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

G. Affectation d'une valeur à une variable de type défini par l'utilisateur en appelant une méthode de 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);

Voir aussi