CASE (Transact-SQL)

Expression CASE simple :

L'expression CASE simple fonctionne en comparant la première expression à l'expression contenue dans chaque clause WHEN pour déterminer son équivalence. Si ces expressions sont équivalentes, l'expression contenue dans la clause THEN est retournée.

• Autorise uniquement un contrôle d'égalité.

• Évalue input_expression et, dans l'ordre spécifié, input_expression = when_expression pour chaque clause WHEN.

• Retourne le result_expression de la première combinaison input_expression = when_expression qui a la valeur TRUE.

• Si aucune comparaison input_expression = 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, en l'absence de clause ELSE, une valeur NULL.

Expression CASE élaborée :

• Évalue, dans l'ordre spécifié, Boolean_expression pour chaque clause WHEN.

• Retourne le result_expression de la première expression Boolean_expression qui a la valeur TRUE.

• Si aucun argument 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, en l'absence de clause ELSE, une valeur NULL.

Dans une instruction SELECT, une expression CASE simple permet seulement de vérifier s'il y a é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 pour 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

Dans une instruction SELECT, l'expression CASE élaboré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

CASE offre une fonctionnalité semblable à 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 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;

L'exemple suivant utilise l'expression CASE dans une clause ORDER BY pour déterminer l'ordre de tri des lignes d'après la valeur d'une colonne donnée. Dans le premier exemple, la valeur de la colonne SalariedFlag de la table HumanResources.Employee est évaluée. Les employés pour lesquels SalariedFlag a la valeur 1 sont retournés par ordre décroissant d'EmployeeID. Les employés pour lesquels SalariedFlag a la valeur 0 sont retournés par ordre croissant d'EmployeeID. Dans le deuxième exemple, le jeu de résultats est classé par la colonne TerritoryName lorsque la colonne CountryRegionName est égale à « United States » et par CountryRegionName pour toutes les autres lignes.

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

SELECT SalesPersonID, LastName, TerritoryName, CountryRegionName
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL
ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName
         ELSE CountryRegionName END;

L'exemple suivant utilise l'expression CASE dans une instruction UPDATE afin de déterminer la valeur définie pour la colonne VacationHours des employés pour lesquels SalariedFlag a la valeur 0. En soustrayant 10 heures des résultats de VacationHours dans une valeur négative, VacationHours augmente de 40 heures ; sinon, VacationHours augmente de 20 heures. La clause OUTPUT est utilisée pour afficher les valeurs antérieures et postérieures aux congés.

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; 

L'exemple suivant utilise l'expression CASE dans une instruction SET au sein de 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, la personne peut être un employé, un représentant du fournisseur, un représentant interne ou un client. La fonction retourne le prénom et le nom d'un ContactID donné, ainsi que le type de contact correspondant à cette personne. L'expression CASE dans l'instruction SET détermine la valeur à afficher pour la colonne ContactType en fonction de l'existence de la colonne ContactID dans les tables Employee, StoreContact, VendorContact ou Individual (client).

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);

L'exemple suivant utilise l'expression CASE dans une clause HAVING pour restreindre les lignes retournées par l'instruction SELECT. L'instruction retourne le taux horaire maximal pour chaque fonction de la table HumanResources.Employee. La clause HAVING restreint les fonctions aux hommes ayant un taux de salaire maximal supérieur à 40 dollars ou aux femmes ayant un taux de salaire maximal 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;