DECLARE @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
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
La syntaxe pour SQL Server et Azure SQL Database est la suivante :
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> | <table_index> } } [ ,...n ] )
<column_definition> ::=
column_name { scalar_data_type | AS computed_column_expression }
[ COLLATE collation_name ]
[ [ DEFAULT constant_expression ] | IDENTITY [ (seed, increment ) ] ]
[ ROWGUIDCOL ]
[ <column_constraint> ]
[ <column_index> ]
<column_constraint> ::=
{
[ NULL | NOT NULL ]
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor
| WITH ( < index_option > [ ,...n ] )
[ ON { filegroup | "default" } ]
| [ CHECK ( logical_expression ) ] [ ,...n ]
}
<column_index> ::=
INDEX index_name [ CLUSTERED | NONCLUSTERED ]
[ WITH ( <index_option> [ ,... n ] ) ]
[ ON { partition_scheme_name (column_name )
| filegroup_name
| default
}
]
[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
<table_constraint> ::=
{
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
( column_name [ ASC | DESC ] [ ,...n ]
[ WITH FILLFACTOR = fillfactor
| WITH ( <index_option> [ ,...n ] )
| [ CHECK ( logical_expression ) ] [ ,...n ]
}
<table_index> ::=
{
{
INDEX index_name [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ]
(column_name [ ASC | DESC ] [ ,... n ] )
| INDEX index_name CLUSTERED COLUMNSTORE
| INDEX index_name [ NONCLUSTERED ] COLUMNSTORE ( column_name [ ,... n ] )
}
[ WITH ( <index_option> [ ,... n ] ) ]
[ ON { partition_scheme_name ( column_name )
| filegroup_name
| default
}
]
[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
}
<index_option> ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| STATISTICS_INCREMENTAL = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
| COMPRESSION_DELAY = { 0 | delay [ Minutes ] }
| DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
[ ON PARTITIONS ( { partition_number_expression | <range> }
[ , ...n ] ) ]
| XML_COMPRESSION = { ON | OFF }
[ ON PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ] ]
}
La syntaxe suivante est destinée à Azure Synapse Analytics, Parallel Data Warehouse et Microsoft Fabric :
DECLARE
{ { @local_variable [AS] data_type } [ = value [ COLLATE <collation_name> ] ] } [ ,...n ]
Arguments
@local_variable
Nom d'une variable. Les noms de variables doivent commencer par le signe @. Les noms de variables locales doivent être conformes aux règles applicables aux 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 un type de données alias. Une variable ne peut pas être de type 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 @ 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>
Un sous-ensemble d’informations utilisé 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 dans 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, une variable ou toute combinaison de ces éléments relié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 soit un nom de classement Windows, soit un nom de classement SQL et s’applique uniquement aux colonnes des types de données 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 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, sauf 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 de ligne unique pour la table. La propriété IDENTITY peut être affectée à des 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 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ésignée comme colonne ROWGUIDCOL. La propriété ROWGUIDCOL ne peut être affectée qu’à une colonne uniqueidentifier.
NULL | NOT NULL
Indique si NULL est autorisé dans la variable. La valeur par défaut est NULL.
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.
CLUSTERED et NONCLUSTERED
Indique qu'un index, cluster ou non cluster, est créé pour la contrainte PRIMARY KEY ou UNIQUE. Les contraintes PRIMARY KEY utilisent CLUSTERED, tandis que les contraintes UNIQUE recourent à NONCLUSTERED.
CLUSTERED peut être spécifié pour une seule contrainte. Si CLUSTERED est spécifié pour une contrainte UNIQUE et qu'une contrainte PRIMARY KEY est également spécifiée, la contrainte PRIMARY KEY utilise NONCLUSTERED.
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.
<index_option>
Spécifie une ou plusieurs options d'index. Il est impossible de créer explicitement des index sur des variables de table et aucune statistique n’est conservée sur les variables de table. À compter de SQL Server 2014 (12.x), une nouvelle syntaxe a été introduite, qui permet de créer certains types d’index inline avec la définition de table. Il est ainsi possible de créer des index sur des variables de table dans le cadre de la définition de la table. Dans certains cas, les performances peuvent s’améliorer en utilisant plutôt des tables temporaires, car elles assurent une prise en charge totale des index et fournissent des statistiques.
Pour une description complète de ces options, consultez CREATE TABLE.
Variables de table et estimations de lignes
Les variables de table n’ont pas de statistiques de distribution. Dans de nombreux cas, l’optimiseur va créer un plan de requête en supposant que la variable de table n’a aucune ligne ou contient une seule ligne. Pour plus d’informations, consultez type de données de table - Limitations et restrictions.
Pour cette raison, soyez prudent lorsque vous utilisez une variable de table si vous attendez un nombre de lignes supérieur à 100. Essayez les alternatives suivantes :
- Les tables temporaires peuvent constituer une meilleure solution que les variables de table lorsqu’il est possible que le nombre de lignes soit plus grand (supérieur à 100).
- Pour les requêtes qui joignent la variable de table à d’autres tables, utilisez l’indicateur RECOMPILE qui contraint l’optimiseur à utiliser la cardinalité correcte pour la variable de table.
- Dans Azure SQL Database, et à partir de SQL Server 2019 (15.x), la fonctionnalité de compilation différée de variable de table propage les estimations de cardinalité fondées sur les nombres de lignes de variables de table réelles, ce qui conduit à un nombre de lignes plus précis pour optimiser le plan d’exécution. Pour plus d’informations, consultez Traitement de requêtes intelligent dans les bases de données SQL.
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 locale correspond au traitement dans lequel elle est déclarée.
Une variable de table ne réside pas nécessairement en mémoire. En cas de sollicitation de la mémoire, les pages appartenant à une variable de table peuvent être envoyées (push) à tempdb.
Vous pouvez définir un index inlined dans une variable de table.
Une variable curseur à laquelle un curseur a été affecté peut être référencée en tant que source dans les instructions suivantes :
- CLOSE (instruction)
- DEALLOCATE (instruction)
- FETCH (instruction)
- OPEN (instruction)
- Instruction positionnée DELETE ou UPDATE
- Instruction variable SET CURSOR (sur le côté droit)
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
R. 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 AdventureWorks2022;
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 le jeu 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 AdventureWorks2022;
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. 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 AdventureWorks2022;
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.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.
--Note that ModifiedDate reflects the value generated by an
--AFTER UPDATE trigger.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO
D. Déclaration d’une variable de type table, avec des index inlined
L’exemple suivant crée une variable table avec un index inlined en cluster et deux index inlined non cluster.
DECLARE @MyTableVar TABLE (
EmpID INT NOT NULL,
PRIMARY KEY CLUSTERED (EmpID),
UNIQUE NONCLUSTERED (EmpID),
INDEX CustomNonClusteredIndex NONCLUSTERED (EmpID)
);
GO
La requête suivante retourne des informations sur les index créés dans la requête précédente.
SELECT *
FROM tempdb.sys.indexes
WHERE object_id < 0;
GO
E. 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;
Exemples : Azure Synapse Analytics et Analytics Platform System (PDW)
F. 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 Walt.
-- Uses AdventureWorks
DECLARE @find VARCHAR(30);
/* Also allowed:
DECLARE @find VARCHAR(30) = 'Man%';
*/
SET @find = 'Walt%';
SELECT LastName, FirstName, Phone
FROM DimEmployee
WHERE LastName LIKE @find;
G. Utilisation de DECLARE avec deux variables
L’exemple suivant récupère des variables pour spécifier les prénoms et noms des employés dans la table DimEmployee.
-- Uses AdventureWorks
DECLARE @lastName VARCHAR(30), @firstName VARCHAR(30);
SET @lastName = 'Walt%';
SET @firstName = 'Bryan';
SELECT LastName, FirstName, Phone
FROM DimEmployee
WHERE LastName LIKE @lastName AND FirstName LIKE @firstName;