DECLARE @local_variable (Transact-SQL)

S’applique à :SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)Point de terminaison d’analytique SQL dans Microsoft Fabric Entrepôt dans Microsoft Fabric Base de données SQL dans Microsoft Fabric Preview

Les variables sont déclarées dans le corps d’un lot ou d’une procédure avec l’instruction DECLARE et sont affectées à des valeurs à l’aide d’une ou SELECT d’une SET instruction. 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 en tant que NULL, sauf si une valeur est fournie dans le cadre de la déclaration.

Conventions de la syntaxe Transact-SQL

Syntax

La syntaxe pour SQL Server et Azure SQL Database est la suivante :

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

<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. Pour plus d’informations sur les types de données définis par l’utilisateur CLR ou les types de données alias, consultez CREATE TYPE.
= valeur

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.

@ 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, NULLet 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>

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. Lors de la déclaration de variables de table , la variable de table doit être la seule variable déclarée 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 à l’aide d’autres colonnes dans 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, une constante, une fonction intégrée, une variable ou une combinaison de ces options connectées 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 et s’applique uniquement aux colonnes des types de données char, varchar, text, nchar, 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. DEFAULT les définitions peuvent être appliquées à n’importe quelle colonne, à l’exception des colonnes définies en tant qu’horodatage ou avec la IDENTITY propriété. DEFAULT les définitions 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, telle qu’un SYSTEM_USER(); ou 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é à un DEFAULT.

constant_expression

Constante, NULLou fonction système utilisée comme valeur par défaut pour la 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 couramment utilisées avec PRIMARY KEY des contraintes pour servir d’identificateur de ligne unique pour la table. La IDENTITY propriété 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. Les valeurs par défaut liées et DEFAULT les contraintes ne peuvent pas être utilisées 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 ROWGUIDCOL colonne. La ROWGUIDCOL propriété 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.

CLÉ PRIMAIRE

Contrainte appliquant l'intégrité d'entité pour une ou plusieurs colonnes données via un index unique. PRIMARY KEY Une seule contrainte 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 avoir plusieurs UNIQUE contraintes.

CLUSTERED et NONCLUSTERED

Indiquez qu’un index cluster ou non cluster est créé pour la contrainte ou UNIQUE la PRIMARY KEY contrainte. PRIMARY KEY les contraintes utilisent CLUSTERED, et UNIQUE les contraintes utilisent NONCLUSTERED.

CLUSTERED peut être spécifié pour une seule contrainte. Si CLUSTERED elle est spécifiée pour une UNIQUE contrainte et qu’une PRIMARY KEY contrainte est également spécifiée, l’utilisation PRIMARY KEY est utilisée 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. SQL Server 2014 (12.x) a introduit une syntaxe qui vous permet de créer certains types d’index inline avec la définition de table. À l’aide de cette syntaxe, vous pouvez créer des index sur des variables de table dans le cadre de la définition de table. Dans certains cas, les performances peuvent s’améliorer à l’aide de tables temporaires à la place, qui fournissent une prise en charge complète des index et 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 génère un plan de requête en supposant que la variable de table a zéro ligne ou une 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 être une meilleure solution que les variables de table lorsqu’il est possible que le nombre de lignes soit supérieur à 100.
Pour les requêtes qui joignent la variable de table à d’autres tables, utilisez l’indicateur RECOMPILE , ce qui fait que l’optimiseur utilise la cardinalité correcte pour la variable de table.
Dans Azure SQL Database et à compter de SQL Server 2019 (15.x), la fonctionnalité de compilation différée de variable de table propage les estimations de cardinalité basées sur les nombres de lignes de variables de table réelles, fournissant 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.

Remarks

Les variables sont souvent utilisées dans un lot ou une procédure comme compteurs pour WHILE, LOOPou pour un IF...ELSE bloc.

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 construire des instructions SQL dynamiques, 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 déclaration
DEALLOCATE déclaration
FETCH déclaration
OPEN déclaration
Position DELETE ou UPDATE instruction
SET CURSOR instruction variable (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.
Peut être référencé comme cible d’un paramètre de curseur de sortie dans une EXECUTE instruction si la variable de curseur n’a pas de curseur actuellement affecté.
doit être considérée comme un pointeur vers le curseur.

Examples

Les exemples de code de cet article utilisent la base de données ou AdventureWorksDW2022 l’exemple AdventureWorks2022 de base de données, que vous pouvez télécharger à partir de la page d’accueil microsoft SQL Server Samples and Community Projects.

A. Utiliser DECLARE

L’exemple suivant utilise une variable locale nommée @find pour récupérer les informations de contact pour tous les noms de famille commençant par Man.

USE AdventureWorks2022;
GO

DECLARE @find AS VARCHAR (30);
/* Also allowed:
DECLARE @find VARCHAR(30) = 'Man%';
*/

SET @find = 'Man%';

SELECT p.LastName,
       p.FirstName,
       ph.PhoneNumber
FROM Person.Person AS p
     INNER JOIN Person.PersonPhone AS ph
         ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE @find;

Voici le jeu de 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

B. Utiliser 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 AS NVARCHAR (50), @Sales AS 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éclarer une variable de table de type

L’exemple suivant crée une table variable qui stocke les valeurs spécifiées dans la OUTPUT clause 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 la clause OUTPUT.

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éclarer une variable de type table, avec des index inline

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éclarer une variable de type de table défini par l’utilisateur

L'exemple suivant crée un paramètre table ou une variable de table portant le nom @LocationTVP. Cette étape 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. Pour plus d’informations sur les paramètres table, consultez Utiliser des paramètres table (moteur de base de données).

DECLARE @LocationTVP AS LocationTableType;

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

F. Utiliser DECLARE

L’exemple suivant utilise une variable locale nommée @find pour récupérer les informations de contact pour tous les noms de famille commençant par Walt.

-- Uses AdventureWorks
DECLARE @find AS VARCHAR (30);
/* Also allowed:
DECLARE @find VARCHAR(30) = 'Man%';
*/

SET @find = 'Walt%';

SELECT LastName,
       FirstName,
       Phone
FROM DimEmployee
WHERE LastName LIKE @find;

G. Utiliser DECLARE avec deux variables

L’exemple suivant récupère des variables pour spécifier les prénoms et les noms de famille des employés dans la DimEmployee table.

DECLARE @lastName AS VARCHAR (30),
    @firstName AS VARCHAR (30);

SET @lastName = 'Walt%';
SET @firstName = 'Bryan';

SELECT LastName,
       FirstName,
       Phone
FROM DimEmployee
WHERE LastName LIKE @lastName
      AND FirstName LIKE @firstName;

Commentaires

Cette page a-t-elle été utile ?

Last updated on 2025-08-16