Variables (Transact-SQL)

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Point de terminaison analytique SQL dans Microsoft FabricEntrepôt dans Microsoft Fabric

Une variable locale Transact-SQL est un objet pouvant posséder une valeur de données unique d’un type donné. Les variables contenues dans les traitements et les scripts sont généralement utilisées :

  • En tant que compteur pour compter le nombre de fois qu’une boucle est effectuée ou pour contrôler le nombre de fois où la boucle est effectuée.
  • pour retenir une valeur de données à tester par une instruction de contrôle de flux ;
  • pour enregistrer une valeur de données que doit retourner le code de retour d'une procédure stockée ou la valeur de retour d'une fonction.

Notes

Le nom de certaines fonctions système Transact-SQL commence par deux arobases (@@). Bien que dans les versions antérieures de SQL Server, les @@ fonctions sont appelées variables globales, @@ les fonctions ne sont pas des variables et n’ont pas les mêmes comportements que les variables. Les @@ fonctions sont des fonctions système et leur utilisation de la syntaxe suit les règles des fonctions.

Vous ne pouvez pas utiliser de variables dans une vue.

Les modifications apportées aux variables ne sont pas affectées par la restauration d’une transaction.

Déclarer une variable Transact-SQL

L’instruction DECLARE initialise une variable Transact-SQL par :

  • Affectation d'un nom. Celui-ci doit avoir comme premier caractère un @ unique.

  • Affectation d'un type de données système ou défini par l'utilisateur, ainsi que d'une taille. Pour les variables numériques, la précision et l'échelle doivent également être affectées. Pour les variables de type XML, une collection de schémas facultative peut être affectée.

  • Définition de la valeur sur NULL.

Par exemple, l’instruction suivante DECLARE crée une variable locale nommée @mycounter avec un type de données int . Par défaut, la valeur de cette variable est NULL.

DECLARE @MyCounter INT;

Pour déclarer plusieurs variables locales, utilisez une virgule après la première variable locale définie, puis indiquez le nom et le type de données de la variable locale suivante.

Par exemple, l’instruction suivante DECLARE crée trois variables locales nommées @LastName, @FirstName et @StateProvinceinitialise chacune à NULL:

DECLARE @LastName NVARCHAR(30), @FirstName NVARCHAR(20), @StateProvince NCHAR(2);

L’étendue d’une variable correspond à la plage des instructions Transact-SQL pouvant référencer cette variable. L’étendue d’une variable dure à partir du point où elle est déclarée jusqu’à la fin du lot ou de la procédure stockée dans laquelle elle est déclarée. Par exemple, le script suivant génère une erreur de syntaxe, car la variable est déclarée dans un lot (séparé par le GO mot clé) et référencée dans une autre :

USE AdventureWorks2022;
GO

DECLARE @MyVariable INT;

SET @MyVariable = 1;
GO

SELECT BusinessEntityID,
    NationalIDNumber,
    JobTitle
FROM HumanResources.Employee
WHERE BusinessEntityID = @MyVariable;

Les variables ont une étendue locale et sont visibles uniquement dans le lot ou la procédure où elles sont définies. Dans l’exemple suivant, l’étendue imbriquée créée pour l’exécution de sp_executesql n’a pas accès à la variable déclarée dans l’étendue supérieure et retourne et erreur.

DECLARE @MyVariable INT;
SET @MyVariable = 1;
EXECUTE sp_executesql N'SELECT @MyVariable'; -- this produces an error

Définir une valeur dans une variable Transact-SQL

Lorsqu’une variable est déclarée pour la première fois, sa valeur est définie sur NULL. Pour affecter une valeur à une variable, utilisez l’instruction SET . C'est la méthode recommandée pour affecter une valeur à une variable. Une variable peut également avoir une valeur affectée en étant référencée dans la liste de sélection d’une SELECT instruction.

Pour affecter une valeur à une variable à l'aide de l'instruction SET, indiquez le nom et la valeur à affecter à la variable. C'est la méthode recommandée pour affecter une valeur à une variable. Le lot suivant, par exemple, déclare deux variables, leur affecte une valeur et les utilise dans la clause WHERE d'une instruction SELECT :

USE AdventureWorks2022;
GO

-- Declare two variables.
DECLARE @FirstNameVariable NVARCHAR(50),
    @PostalCodeVariable NVARCHAR(15);

-- Set their values.
SET @FirstNameVariable = N'Amy';
SET @PostalCodeVariable = N'BA5 3HX';

-- Use them in the WHERE clause of a SELECT statement.
SELECT LastName,
    FirstName,
    JobTitle,
    City,
    StateProvinceName,
    CountryRegionName
FROM HumanResources.vEmployee
WHERE FirstName = @FirstNameVariable
    OR PostalCode = @PostalCodeVariable;
GO

Vous pouvez également affecter une valeur à une variable en y faisant référence dans une liste de sélection. Si une variable est référencée dans une liste de sélection, elle doit être affectée à une valeur scalaire ou l’instruction SELECT ne doit retourner qu’une seule ligne. Par exemple :

USE AdventureWorks2022;
GO
DECLARE @EmpIDVariable INT;

SELECT @EmpIDVariable = MAX(EmployeeID)
FROM HumanResources.Employee;
GO

Avertissement

S’il existe plusieurs clauses d’affectation dans une seule SELECT instruction, SQL Server ne garantit pas l’ordre d’évaluation des expressions. Les effets ne sont visibles que s’il existe des références entre les affectations.

Si une SELECT instruction retourne plusieurs lignes et que la variable fait référence à une expression noncalaire, la variable est définie sur la valeur retournée pour l’expression dans la dernière ligne du jeu de résultats. Par exemple, dans le lot @EmpIDVariable suivant, la valeur de la dernière ligne retournée est BusinessEntityID1:

USE AdventureWorks2022;
GO
DECLARE @EmpIDVariable INT;

SELECT @EmpIDVariable = BusinessEntityID
FROM HumanResources.Employee
ORDER BY BusinessEntityID DESC;

SELECT @EmpIDVariable;
GO

Exemple

Le script suivant crée une petite table test et lui attribue 26 lignes. Il utilise une variable pour effectuer trois actions :

  • vérifier le nombre de lignes insérées en contrôlant combien de fois la boucle est exécutée ;
  • fournir la valeur insérée dans la colonne INT ;
  • faire partie de l'expression qui génère les lettres devant être insérées dans la colonne CHAR.
-- Create the table.
CREATE TABLE TestTable (cola INT, colb CHAR(3));
GO

SET NOCOUNT ON;
GO

-- Declare the variable to be used.
DECLARE @MyCounter INT;

-- Initialize the variable.
SET @MyCounter = 0;

-- Test the variable to see if the loop is finished.
WHILE (@MyCounter < 26)
BEGIN;
    -- Insert a row into the table.
    INSERT INTO TestTable
    VALUES
        -- Use the variable to provide the integer value
        -- for cola. Also use it to generate a unique letter
        -- for each row. Use the ASCII function to get the
        -- integer value of 'a'. Add @MyCounter. Use CHAR to
        -- convert the sum back to the character @MyCounter
        -- characters after 'a'.
        (
        @MyCounter,
        CHAR((@MyCounter + ASCII('a')))
        );

    -- Increment the variable to count this iteration
    -- of the loop.
    SET @MyCounter = @MyCounter + 1;
END;
GO

SET NOCOUNT OFF;
GO

-- View the data.
SELECT cola, colb FROM TestTable;
GO

DROP TABLE TestTable;
GO

Contenu connexe