CASE (Transact-SQL)

  • Article

Mis à jour : 14 avril 2006

Évalue une liste de conditions et retourne l'une des expressions de résultats possibles.

L'expression CASE possède deux formats :

  • L'expression CASE simple détermine le résultat en comparant une expression à un jeu d'expressions simples.
  • L'expression CASE recherchée détermine le résultat en évaluant un jeu d'expressions booléennes.

Les deux formats prennent en charge un argument ELSE facultatif.

L'expression CASE peut être utilisée dans toute instruction ou clause qui autorise une expression valide. Par exemple, vous pouvez utiliser l'expression CASE dans des instructions comme SELECT, UPDATE, DELETE et SET, ainsi que dans des clauses comme select_list, IN, WHERE, ORDER BY et HAVING.

Icône Lien de rubriqueConventions de syntaxe Transact-SQL

Syntaxe

Simple CASE expression: 
CASE input_expression 
     WHEN when_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END 
Searched CASE expression:
CASE
     WHEN Boolean_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

Arguments

  • input_expression
    Expression évaluée lorsque le format CASE simple est utilisé. input_expression peut être toute expression valide.
  • WHEN when_expression
    Expression simple à laquelle input_expression est comparé lorsque le format CASE simple est utilisé. when_expression peut être toute expression valide. Les types de données de input_expression et de chaque when_expression doivent être identiques ou permettre une conversion implicite.
  • THEN result_expression
    Expression retournée lorsque input_expression égal à when_expression a la valeur TRUE ou que Boolean_expression a la valeur TRUE. result expression peut être toute expression valide.
  • ELSE else_result_expression
    Expression retournée si aucune opération de comparaison n'a la valeur TRUE. Si cet argument est omis et qu'aucune opération de comparaison n'a la valeur TRUE, CASE retourne la valeur NULL. else_result_expression peut être toute expression valide. Les types de données de else_result_expression et de tout result_expression doivent être identiques ou permettre une conversion implicite.
  • WHEN Boolean_expression
    Expression booléenne évaluée lorsque le format CASE recherché est utilisé. Boolean_expression peut être toute expression booléenne valide.

Notes

SQL Server n'autorise que 10 niveaux d'imbrication dans les expressions CASE.

L'expression CASE ne permet pas de contrôler le flux d'exécution des instructions Transact-SQL, les blocs d'instructions, les fonctions définies par l'utilisateur et les procédures stockées. Pour obtenir la liste des méthodes de contrôle de flux, consultez Langage de contrôle de flux (Transact-SQL).

Types des résultats

Retourne le type de la précédence la plus élevée parmi l'ensemble des types dans result_expressions, ainsi que le paramètre else_result_expression facultatif. Pour plus d'informations, consultez Priorités des types de données (Transact-SQL).

Valeurs des résultats

Expression CASE simple :

L'expression CASE simple compare la première expression à l'expression de chaque clause WHEN pour y rechercher une équivalence. Si ces expressions sont équivalentes, l'expression de la clause WHEN est retournée.

  • Autorise uniquement un contrôle d'égalité.
  • Évalue input_expression puis, dans l'ordre spécifié, évalue input_expression égal à when_expression pour chaque clause WHEN.
  • Retourne le result_expression du premier input_expression égal à when_expression ayant la valeur TRUE.
  • Si aucun input_expression égal à when_expression n'a la valeur TRUE, le moteur de base de données SQL Server retourne else_result_expression si une clause ELSE est spécifiée ou une valeur NULL si aucune clause ELSE n'est spécifiée.

Expression CASE recherchée :

  • Évalue, dans l'ordre spécifié, Boolean_expression pour chaque clause WHEN.
  • Retourne le result_expression du premier Boolean_expression ayant la valeur TRUE.
  • Si aucun Boolean_expression n'a la valeur TRUE, le moteur de base de données retourne else_result_expression si une clause ELSE est spécifiée ou la valeur NULL si aucune clause ELSE n'est spécifiée.

Exemples

A. Utilisation d'une instruction SELECT avec une expression CASE simple

Dans une instruction SELECT, une expression CASE simple permet uniquement d'assurer un contrôle d'égalité. Aucune autre comparaison n'est effectuée. L'exemple suivant utilise l'expression CASE pour modifier la présentation des catégories de gammes de produits afin d'en faciliter la lecture.

USE AdventureWorks;
GO
SELECT   ProductNumber, Category =
      CASE ProductLine
         WHEN 'R' THEN 'Road'
         WHEN 'M' THEN 'Mountain'
         WHEN 'T' THEN 'Touring'
         WHEN 'S' THEN 'Other sale items'
         ELSE 'Not for sale'
      END,
   Name
FROM Production.Product
ORDER BY ProductNumber;
GO

B. Utilisation d'une instruction SELECT avec une expression CASE recherchée

Dans une instruction SELECT, l'expression CASE recherchée permet de remplacer des valeurs dans le jeu de résultats, en fonction des valeurs de comparaison. L'exemple suivant donne le prix sous la forme d'un texte de commentaire basé sur la fourchette de prix d'un produit.

USE AdventureWorks;
GO
SELECT   ProductNumber, Name, 'Price Range' = 
      CASE 
         WHEN ListPrice =  0 THEN 'Mfg item - not for resale'
         WHEN ListPrice < 50 THEN 'Under $50'
         WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
         WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
         ELSE 'Over $1000'
      END
FROM Production.Product
ORDER BY ProductNumber ;
GO

C. Utilisation de l'expression CASE pour remplacer la fonction IIf utilisée dans Microsoft Access

L'expression CASE propose une fonctionnalité similaire à la fonction IIf dans Microsoft Access. L'exemple suivant montre une requête simple qui utilise IIf pour fournir une valeur de sortie pour la colonne TelephoneInstructions dans une table Access nommée db1.ContactInfo.

SELECT FirstName, LastName, TelephoneNumber, 
     IIf(IsNull(TelephoneInstructions),"Any time",
     TelephoneInstructions) AS [When to Contact]
FROM db1.ContactInfo;

L'exemple suivant utilise l'expression CASE pour fournir une valeur de sortie pour la colonne TelephoneSpecialInstructions dans la vue AdventureWorks, Person.vAdditionalContactInfo.

USE AdventureWorks;
GO
SELECT FirstName, LastName, TelephoneNumber, 'When to Contact' = 
     CASE
          WHEN TelephoneSpecialInstructions IS NULL THEN 'Any time'
          ELSE TelephoneSpecialInstructions
     END
FROM Person.vAdditionalContactInfo;

D. Utilisation de l'expression CASE dans une clause ORDER BY

L'exemple suivant utilise l'expression CASE dans une clause ORDER BY pour déterminer l'ordre de tri des lignes en fonction de la valeur qui figure dans la colonne SalariedFlag de la table HumanResources.Employee. Les employés pour lesquels SalariedFlag a la valeur 1 sont retournés par EmployeeID dans l'ordre décroissant. Les employés pour lesquels SalariedFlag a la valeur 0 sont retournés par EmployeeID dans l'ordre croissant.

SELECT EmployeeID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag WHEN 1 THEN EmployeeID END DESC
        ,CASE WHEN SalariedFlag = 0 THEN EmployeeID END;
GO

E. Utilisation de l'expression CASE dans une instruction UPDATE

L'exemple suivant utilise l'expression CASE dans une instruction UPDATE pour déterminer la valeur qui est définie pour la colonne VacationHours pour les employés pour lesquels SalariedFlag a la valeur 0. Si une valeur négative est obtenue suite à la soustraction de 10 heures de VacationHours, VacationHours est augmenté de 40 heures ; sinon, VacationHours est augmenté de 20 heures. La clause OUTPUT permet d'afficher les valeurs avant et après les vacances.

USE AdventureWorks;
GO
UPDATE HumanResources.Employee
SET VacationHours = 
    ( CASE
         WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
         ELSE (VacationHours + 20.00)
       END
    )
OUTPUT Deleted.EmployeeID, Deleted.VacationHours AS BeforeValue, 
       Inserted.VacationHours AS AfterValue
WHERE SalariedFlag = 0;

F. Utilisation de l'expression CASE dans une instruction SET

L'exemple suivant utilise l'expression CASE dans une instruction SET dans la fonction table dbo.GetContactInfo. Dans la base de données AdventureWorks, toutes les données relatives aux personnes sont stockées dans la table Person.Contact. Par exemple, une personne peut être un employé, un fournisseur, un responsable de magasin ou un client. La fonction retourne le nom et le prénom d'un ContactID donné et le type de contact de cette personne. L'expression CASE dans l'instruction SET détermine la valeur à afficher pour la colonne ContactType en fonction de l'existence de ContactID dans les tables Employee, StoreContact, VendorContact ou Individual (clients).

USE AdventureWorks;
GO
CREATE FUNCTION dbo.GetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE 
(
    ContactID int NOT NULL,
    FirstName nvarchar(50) NULL,
    LastName nvarchar(50) NULL,
    ContactType nvarchar(50) NULL,
    PRIMARY KEY CLUSTERED (ContactID ASC)
) 
AS 
-- Returns the first name, last name and contact type for the specified contact.
BEGIN
    DECLARE 
        @FirstName nvarchar(50), 
        @LastName nvarchar(50), 
        @ContactType nvarchar(50);

    -- Get common contact information
    SELECT 
        @ContactID = ContactID, 
        @FirstName = FirstName, 
        @LastName = LastName
    FROM Person.Contact 
    WHERE ContactID = @ContactID;

    SET @ContactType = 
        CASE 
            -- Check for employee
            WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e 
                WHERE e.ContactID = @ContactID) 
                THEN 'Employee'

            -- Check for vendor
            WHEN EXISTS(SELECT * FROM Purchasing.VendorContact AS vc 
                    INNER JOIN Person.ContactType AS ct 
                    ON vc.ContactTypeID = ct.ContactTypeID 
                WHERE vc.ContactID = @ContactID) 
                THEN 'Vendor Contact'

            -- Check for store
            WHEN EXISTS(SELECT * FROM Sales.StoreContact AS sc 
                    INNER JOIN Person.ContactType AS ct 
                    ON sc.ContactTypeID = ct.ContactTypeID 
                WHERE sc.ContactID = @ContactID) 
                THEN 'Store Contact'

            -- Check for individual consumer
            WHEN EXISTS(SELECT * FROM Sales.Individual AS i 
                WHERE i.ContactID = @ContactID) 
                THEN 'Consumer'
        END;

    -- Return the information to the caller
    IF @ContactID IS NOT NULL 
    BEGIN
        INSERT @retContactInformation
        SELECT @ContactID, @FirstName, @LastName, @ContactType;
    END;

    RETURN;
END;
GO
SELECT ContactID, FirstName, LastName, ContactType
FROM dbo.GetContactInformation(2200);
GO
SELECT ContactID, FirstName, LastName, ContactType
FROM dbo.GetContactInformation(5);

G. Utilisation de l'expression CASE dans une clause HAVING

L'exemple suivant utilise l'expression CASE dans une clause HAVING pour limiter les lignes retournées par l'instruction SELECT. L'instruction retourne le taux horaire maximal pour chaque fonction dans la table HumanResources.Employee. La clause HAVING limite les fonctions à celles qui concernent les hommes dont le taux de rémunération maximal est supérieur à 40 dollars ou qui concernent les femmes dont le taux de rémunération maximal est supérieur à 42 dollars.

USE AdventureWorks;
GO
SELECT Title, MAX(ph1.Rate)AS MaximumRate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS ph1 ON e.EmployeeID = ph1.EmployeeID
GROUP BY Title
HAVING (MAX(CASE WHEN Gender = 'M' 
        THEN ph1.Rate 
        ELSE NULL END) > 40.00
     OR MAX(CASE WHEN Gender  = 'F' 
        THEN ph1.Rate  
        ELSE NULL END) > 42.00)
ORDER BY MaximumRate DESC;

Voir aussi

Référence

Expressions (Transact-SQL)
SELECT (Transact-SQL)
COALESCE (Transact-SQL)

Autres ressources

Utilisation de CASE

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications

Version Historique

14 avril 2006
Nouveau contenu :
  • Ajout d'un exemple qui illustre la fonctionnalité IIf à l'aide de CASE.