DECLARE @local_variable (Transact-SQL)

Mis à jour : 5 décembre 2005

Les variables locales sont déclarées au sein d'un lot d'instructions ou d'une procédure avec l'instruction DECLARE, et l'instruction SET ou SELECT leur affecte des valeurs. Les variables curseur peuvent être déclarées avec cette instruction, puis utilisées avec d'autres instructions liées aux curseurs. Après la déclaration, toutes les variables sont initialisées avec la valeur NULL.

Conventions de la syntaxe de Transact-SQL

Syntaxe

DECLARE 
     {{ @local_variable [AS] data_type } 
    | { @cursor_variable_name CURSOR } 
    | { @table_variable_name [AS] < table_type_definition > } 
     } [ ,...n]

< table_type_definition > ::= 
     TABLE ( { < column_definition > | < table_constraint > } [ ,... ] 
      ) 

< column_definition > ::= 
          column_name { scalar_data_type | AS computed_column_expression }
     [ COLLATE collation_name ] 
     [ [ DEFAULT constant_expression ] | IDENTITY [ ( seed,increment ) ] ] 
     [ ROWGUIDCOL ] 
     [ < column_constraint > ] 

< column_constraint > ::= 
     { [ NULL | NOT NULL ] 
     | [ PRIMARY KEY | UNIQUE ] 
     | CHECK ( logical_expression ) 
     } 

< table_constraint > ::= 
     { { PRIMARY KEY | UNIQUE } ( column_name [ ,... ] ) 
     | CHECK ( search_condition ) 
     } 

Arguments

• ****@**local_variable
  Nom d'une variable. Les noms de variables doivent commencer par le signe @. Les noms de variables locales doivent respecter les conventions se rapportant aux identificateurs. Pour plus d'informations, consultez Utilisation des identificateurs comme noms d'objet.

• data_type
  Il s'agit de tout type CLR (Common Language Runtime) défini par l'utilisateur, fourni par le système, ou type de données alias. Une variable ne peut pas avoir un type de données text, ntext ou image. Pour plus d'informations sur les types de données système, consultez Types de données. Pour plus d'informations sur les types de données CLR définis par l'utilisateur ou les types de données alias, consultez CREATE TYPE (Transact-SQL).

• ****@**cursor_variable_name
  Nom d'une variable curseur. Les noms de variables curseur doivent commencer par le signe @ et être conformes aux règles des identificateurs.

• CURSOR
  Indique que la variable est une variable curseur locale.

• @table_variable_name
  Nom d'une variable de type table. Les noms de variables doivent commencer par le signe @ et être conformes aux règles des identificateurs.

• table_type_definition
  Définit le type de données table. La déclaration de table inclut des définitions de colonnes, des noms, des types de données et des contraintes. Les seuls types de contraintes autorisés sont PRIMARY KEY, UNIQUE, NULL et CHECK. Un type de données alias ne peut pas être utilisé comme type de données scalaire de colonne si une règle ou une définition par défaut est liée au type.

  table_type_definition est un sous-ensemble d'informations utilisées pour définir une table dans CREATE TABLE. Les éléments et les définitions essentielles sont inclus ici. Pour plus d'informations, consultez CREATE TABLE.

• n
  Espace réservé indiquant que plusieurs variables peuvent être spécifiées et que des valeurs peuvent leur être affectées. Lorsque vous déclarez des variables table, seules les variables table doivent être déclarées dans l'instruction DECLARE.

• column_name
  Nom de la colonne de la table.

• scalar_data_type
  Spécifie que la colonne est un type de données scalaire.

• computed_column_expression
  Expression définissant la valeur d'une colonne calculée. Elle est calculée à partir d'une expression qui utilise d'autres colonnes de la même table. Par exemple, une colonne calculée peut avoir la définition cost AS price * qty. L'expression peut être un nom de colonne non calculée, une constante, une fonction intégrée ou une combinaison de ces éléments connectés par un ou plusieurs opérateurs. L'expression ne peut pas être une sous-requête ou une fonction définie par l'utilisateur. L'expression ne peut pas faire référence à un type CLR défini par l'utilisateur.

• [ COLLATE collation_name ]
  Définit le classement de la colonne. collation_name peut être un nom de classement Windows ou SQL, et est applicable seulement aux colonnes de type char, varchar, text, nchar, nvarchar et ntext. Si cette valeur n'est pas spécifiée, la colonne reçoit le classement du type de données utilisateur (si la colonne est de type de données utilisateur), ou le classement de la base de données active.

  Pour plus d'informations sur les noms de classement Windows et SQL, consultez COLLATE.

• DEFAULT
  Spécifie la valeur fournie pour la colonne lorsque vous n'avez pas spécifié explicitement de valeur lors d'une insertion. Les définitions DEFAULT peuvent être appliquées à n'importe quelle colonne, sauf à celles définies sous la forme timestamp ou ayant la propriété IDENTITY. Les définitions de valeurs par défaut sont supprimées lorsque la table est supprimée. Seule une valeur constante, telle qu'une chaîne de caractères, une fonction système comme SYSTEM_USER() ou la valeur NULL, peut être utilisée comme valeur par défaut. Pour maintenir la compatibilité avec les versions antérieures de SQL Server, un nom de contrainte peut être affecté à une définition DEFAULT.

• constant_expression
  Constante, valeur NULL ou fonction système utilisée comme valeur par défaut pour une colonne.

• IDENTITY
  Indique que la nouvelle colonne est une colonne d'identité. Lorsqu'une nouvelle ligne est ajoutée à la table, SQL Server fournit une valeur incrémentielle unique pour la colonne. Les colonnes d'identité sont normalement utilisées avec les contraintes PRIMARY KEY comme identificateur unique de ligne pour la table. La propriété IDENTITY peut être affectée à des colonnes tinyint, smallint, int, decimal(p,0) et numeric(p,0). Une seule colonne d'identité peut être créée par table. Il n'est pas possible d'utiliser des valeurs par défaut liées et des contraintes DEFAULT avec une colonne d'identité. Vous devez spécifier à la fois la valeur initiale et l'incrément, ou bien aucun des deux. Si vous n'en spécifiez aucun, la valeur par défaut est (1,1).

• seed
  Valeur utilisée pour la toute première ligne chargée dans la table.

• increment
  Valeur d'incrément ajoutée à la valeur d'identité de la ligne précédemment chargée.

• ROWGUIDCOL
  Indique que la nouvelle colonne est une colonne d'identificateur unique global de ligne. Une seule colonne uniqueidentifier par table peut être définie comme colonne ROWGUIDCOL. La propriété ROWGUIDCOL ne peut être affectée qu'à une colonne uniqueidentifier.

• NULL | NOT NULL
  Mots clés déterminant si les valeurs NULL sont autorisées ou non dans une colonne.

• PRIMARY KEY
  Contrainte appliquant l'intégrité d'entité pour une ou plusieurs colonnes données via un index unique. Une seule contrainte PRIMARY KEY peut être créée par table.

• UNIQUE
  Contrainte fournissant l'intégrité d'entité pour une ou plusieurs colonnes données via un index unique. Une table peut comprendre plusieurs contraintes UNIQUE.

• CHECK
  Contrainte qui assure l'intégrité de domaine en limitant les valeurs qui peuvent être entrées dans une ou plusieurs colonnes.

• logical_expression
  Expression logique qui renvoie TRUE ou FALSE.

Notes

Les variables locales sont souvent utilisées dans un lot d'instructions ou une procédure comme compteurs pour une boucle WHILE, LOOP ou pour un bloc IF...ELSE.

Les variables ne peuvent être utilisées que dans des expressions et pas la place de noms d'objets ou de mots clés. Pour créer des instructions dynamiques SQL, utilisez EXECUTE.

La portée d'une variable correspond au lot dans lequel elle est déclarée.

Une variable curseur à laquelle un curseur a été affecté peut être référencée en tant que source dans les instructions suivantes :

• Instruction CLOSE.
• Instruction DEALLOCATE.
• Instruction FETCH.
• Instruction OPEN.
• Instruction positionnée DELETE ou UPDATE.
• Instruction variable (sur le côté droit) SET CURSOR.

Dans toutes ces instructions, Microsoft SQL Server génère une erreur si une variable curseur référencée existe et si un curseur ne lui est pas alloué. Si une variable curseur référencée n'existe pas, SQL Server génère la même erreur que pour une variable non déclarée d'un autre type.

Les valeurs d'une variable curseur ne changent pas après la déclaration d'un curseur. Dans SQL Server version 6.5 et les versions précédentes, les valeurs de la variable sont actualisées chaque fois que le curseur est ouvert.

Une variable de curseur :

• peut être la cible d'une autre type de curseur ou d'une autre variable curseur ; Pour plus d'informations, consultez SET @local\_variable.
• peut être référencée en tant que cible d'un paramètre de curseur de sortie dans une instruction EXECUTE si aucun curseur ne lui est actuellement affecté ;
• doit être considérée comme un pointeur vers le curseur. Pour plus d'informations sur les variable curseur, consultez Curseurs Transact-SQL.

Exemples

A. Utilisation de DECLARE

L'exemple suivant utilise la variable locale @find pour extraire les informations de contact de tous les noms commençant par 'Man'.

USE AdventureWorks;
GO
DECLARE @find varchar(30);
SET @find = 'Man%';
SELECT LastName, FirstName, Phone
FROM Person.Contact
WHERE LastName LIKE @find;

Voici l'ensemble de résultats obtenu :

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. Utilisation de DECLARE avec deux variables

L'exemple suivant extrait les noms des vendeurs de Adventure Works Cycles qui se trouvent sur le secteur de vente North American et qui génèrent un chiffre d'affaires annuel minimum de 2 000 000 de dollars.

USE AdventureWorks;
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. Utilisation de DECLARE pour déclarer une variable de type table

L'exemple suivant crée une variable table qui stocke les valeurs définies dans la clause OUTPUT de l'instruction UPDATE. Deux instructions SELECT suivent, qui renvoient les valeurs dans @MyTableVar ainsi que les résultats de la mise à jour dans la table Employee. Notez que les résultats dans la colonne INSERTED.ModifiedDate sont différents des valeurs de la colonne ModifiedDate dans la table Employee . Ceci s'explique par le fait que le déclencheur AFTER UPDATE, qui met à jour la valeur de ModifiedDate en fonction de la date actuelle, est défini sur la table Employee. Toutefois, les colonnes renvoyées par OUTPUT reflètent les données avant l'activation des déclencheurs. Pour plus d'exemples sur l'utilisation de la clause OUTPUT, consultez Clause OUTPUT (Transact-SQL).

USE AdventureWorks;
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.EmployeeID,
       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) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

Voir aussi

Référence

EXECUTE (Transact-SQL)
Fonctions (Transact-SQL)
SELECT (Transact-SQL)
table (Transact-SQL)

Autres ressources

XML typé et non typé
Modification des valeurs de paramètres et de variables d'un type de données défini par l'utilisateur

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications