DECLARE @local_variable (Transact-SQL)

Les variables sont déclarées au sein d'un lot 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, à moins qu'une valeur ne soit fournie dans le cadre de la déclaration.

Conventions de la syntaxe Transact-SQL

Syntaxe

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

<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 ) 
     | WITH ( <index_option > )
     } 

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

<index_option> ::=
See CREATE TABLE for index option syntax.

Arguments

• @local\_variable
  Nom d'une variable. Les noms de variables doivent commencer par le signe arobase (@) Les noms de variables locales doivent être conformes aux règles des identificateurs.

• data_type
  Il s'agit de tout type de table 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 être du 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 (Transact-SQL). 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).

• =value
  Attribue une valeur à la variable en ligne. La valeur peut être une constante ou une expression, mais elle doit soit correspondre au type de déclaration de la variable, soit être implicitement convertible vers ce type. Pour plus d'informations, consultez Expressions (Transact-SQL).

• @cursor\_variable\_name
  Nom d'une variable curseur. Les noms de variables curseur doivent commencer par le signe arobase (@) 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 arobase (@) 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 (Transact-SQL).

• 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]
  Indique le classement de la colonne. collation_name peut être un nom de classement Windows ou un nom de classement SQL. Il n'est applicable qu'aux colonnes avec le type de données char, varchar, text, nchar, nvarchar ou 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 classements Windows et SQL, consultez COLLATE (Transact-SQL).

• 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 exceptée celles définies en tant que timestamp ou celles dotées de 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 attribuée aux colonnes tinyint, smallint, int, decimal(p,0) ou 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 servir de 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 la 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é du domaine en limitant les valeurs possibles pouvant être entrées dans une ou plusieurs colonnes.

• logical_expression
  Expression logique qui retourne TRUE ou FALSE.

Notes

Les variables locales sont souvent utilisées dans un traitement 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 traitement 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, SQL Server génère une erreur si une variable curseur référencée existe et qu'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.

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 (Transact-SQL).

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

Exemples

A.Utilisation de DECLARE

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

USE AdventureWorks2012;
GO
DECLARE @find varchar(30); 
/* Also allowed: 
DECLARE @find varchar(30) = 'Man%'; 
*/
SET @find = 'Man%'; 
SELECT p.LastName, p.FirstName, ph.PhoneNumber
FROM Person.Person AS p 
JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE @find; 

Voici l'ensemble des résultats.

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 AdventureWorks2012;
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.Déclaration d'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 ; elles retournent 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'informations, consultez Clause OUTPUT (Transact-SQL).

USE AdventureWorks2012;
GO
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
    ModifiedDate = GETDATE() 
OUTPUT inserted.BusinessEntityID,
       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.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

D.Déclaration d'une variable de type table défini par l'utilisateur

L'exemple suivant crée un paramètre table ou une variable de table portant le nom @LocationTVP. Cela nécessite un type de table défini par l'utilisateur correspondant appelé LocationTableType. Pour plus d'informations sur la création d'un type de table défini par l'utilisateur, consultez CREATE TYPE (Transact-SQL). Pour plus d'informations sur les paramètres table, consultez Utiliser les paramètres table (Moteur de base de données).

DECLARE @LocationTVP AS LocationTableType;

Voir aussi

Référence

EXECUTE (Transact-SQL)

Fonctions intégrées (Transact-SQL)

SELECT (Transact-SQL)

table (Transact-SQL)

Concepts

Comparer du XML typé et du XML non typé