CASE (Transact-SQL)
Valuta un elenco di condizioni e restituisce una tra più espressioni di risultato possibili.
L'espressione CASE ha due formati:
L'espressione CASE semplice confronta un'espressione con un set di espressioni semplici per determinare il risultato.
L'espressione CASE avanzata valuta un set di espressioni booleane per determinare il risultato.
Entrambi i formati supportano un argomento facoltativo ELSE.
L'espressione CASE può essere utilizzata in qualsiasi istruzione o clausola che consenta un'espressione valida. È possibile, ad esempio, utilizzare CASE in istruzioni quali SELECT, UPDATE, DELETE e SET e in clausole quali select_list, IN, WHERE, ORDER BY e HAVING.
.gif "Icona di collegamento a un argomento")
Sintassi
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
Argomenti
input_expression
Espressione valutata quando si utilizza il formato semplice dell'espressione CASE. input_expression è una qualsiasi espressione valida.WHEN when_expression
Espressione semplice con cui viene confrontato input_expression quando si utilizza il formato semplice dell'espressione CASE. when_expression è una qualsiasi espressione valida. A input_expression e a ogni occorrenza di when_expression deve essere associato lo stesso tipo di dati o un tipo di dati convertibile in modo implicito.THEN result_expression
Espressione restituita quando il confronto di uguaglianza tra input_expression e when_expression restituisce TRUE oppure quando Boolean_expression restituisce TRUE. result expression è una qualsiasi espressione valida.ELSE else_result_expression
Espressione restituita quando nessuna operazione di confronto restituisce TRUE. Se questo argomento viene omesso e nessuna operazione di confronto restituisce TRUE, CASE restituisce NULL. else_result_expression è una qualsiasi espressione valida. A else_result_expression e a ogni occorrenza di result_expression deve essere associato lo stesso tipo di dati o un tipo di dati convertibile in modo implicito.WHEN Boolean_expression
Espressione booleana valutata quando si utilizza il formato avanzato dell'espressione CASE. Boolean_expression è una qualsiasi espressione booleana valida.
Tipi restituiti
Restituisce il tipo con precedenza maggiore nel set di tipi di result_expressions e dell'argomento facoltativo else_result_expression. Per ulteriori informazioni, vedere Precedenza dei tipi di dati (Transact-SQL).
Valori restituiti
Espressione CASE semplice:
L'espressione CASE semplice confronta la prima espressione con l'espressione in ogni clausola WHEN per determinarne l'equivalenza. Se le espressioni sono equivalenti, viene restituita l'espressione nella clausola THEN.
Consente solo un controllo di uguaglianza.
Valuta input_expression, quindi, nell'ordine specificato, valuta il risultato di input_expression = when_expression per ogni clausola WHEN.
Restituisce l'argomento result_expression del primo confronto input_expression = when_expression che restituisce TRUE.
Se nessun confronto input_expression = when_expression restituisce TRUE, il Motore di database di SQL Server restituisce else_result_expression se è stata specificata una clausola ELSE. In caso contrario, restituisce un valore NULL.
Espressione CASE avanzata:
Valuta nell'ordine specificato Boolean_expression per ogni clausola WHEN.
Restituisce l'argomento result_expression della prima espressione Boolean_expression che restituisce TRUE.
Se nessuna espressione Boolean_expression restituisce TRUE, il Motore di database restituisce else_result_expression se è stata specificata una clausola ELSE. In caso contrario, restituisce un valore NULL.
Osservazioni
SQL Server consente solo 10 livelli di nidificazione nelle espressioni CASE.
L'espressione CASE non può essere utilizzata per controllare il flusso di esecuzione di istruzioni, blocchi di istruzioni, funzioni definite dall'utente e stored procedure Transact-SQL. Per un elenco dei metodi di controllo di flusso, vedere Elementi del linguaggio per il controllo di flusso (Transact-SQL).
Esempi
A. Utilizzo di un'istruzione SELECT con un'espressione CASE semplice
In un'istruzione SELECT un'espressione CASE semplice consente di eseguire solo un controllo di uguaglianza senza ulteriori confronti. Nell'esempio seguente viene utilizzata l'espressione CASE per modificare la visualizzazione delle categorie delle linee di prodotti in modo da renderle più intuitive.
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. Utilizzo di un'istruzione SELECT con un'espressione CASE avanzata
In un'istruzione SELECT l'espressione CASE avanzata consente di sostituire valori nel set di risultati in base ai valori di confronto. Nell'esempio seguente viene visualizzato il prezzo di listino come commento di testo in base alla fascia di prezzi per un prodotto..
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. Utilizzo di CASE in sostituzione della funzione IIf utilizzata in Microsoft Access
CASE offre funzionalità simili alla funzione IIf di Microsoft Access. Nell'esempio seguente viene illustrata una query semplice che utilizza IIf per ottenere un valore di output per la colonna TelephoneInstructions di una tabella di Access denominata db1.ContactInfo.
SELECT FirstName, LastName, TelephoneNumber,
IIf(IsNull(TelephoneInstructions),"Any time",
TelephoneInstructions) AS [When to Contact]
FROM db1.ContactInfo;
Nell'esempio seguente viene utilizzata l'espressione CASE per ottenere un valore di output per la colonna TelephoneSpecialInstructions nella vista Person.vAdditionalContactInfo di AdventureWorks.
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. Utilizzo di CASE in una clausola ORDER BY
Nell'esempio seguente viene utilizzata l'espressione CASE in una clausola ORDER BY per determinare l'ordinamento delle righe in base al valore di una colonna. Nel primo esempio, viene calcolato il valore nella colonna SalariedFlag della tabella HumanResources.Employee. I dipendenti per cui SalariedFlag è impostato su 1 vengono restituiti in ordine decrescente in base a EmployeeID. I dipendenti per cui SalariedFlag è impostato su 0 vengono restituiti in ordine crescente in base a EmployeeID. Nel secondo esempio, il set di risultati viene ordinato in base alla colonna TerritoryName quando la colonna CountryRegionName è uguale a "Stati Uniti" e in base a CountryRegionName per tutte le altre righe.
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;
E. Utilizzo di CASE in un'istruzione UPDATE
Nell'esempio seguente viene utilizzata l'espressione CASE in un'istruzione UPDATE per determinare il valore impostato per la colonna VacationHours per i dipendenti per cui SalariedFlag è impostato su 0. Se sottraendo 10 ore da VacationHours viene restituito un valore negativo, VacationHours viene aumentato di 40 ore; in caso contrario, VacationHours viene aumentato di 20 ore. La clausola OUTPUT viene utilizzata per visualizzare i valori precedenti e successivi alle ferie.
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;
E. Utilizzo di CASE in un'istruzione SET
Negli esempi seguenti viene utilizzata l'espressione CASE in un'istruzione SET nella funzione dbo.GetContactInfo con valori di tabella. Nel database AdventureWorks tutti i dati correlati alle persone vengono archiviati nella tabella Person.Contact. La persona, ad esempio, può essere un dipendente, un rappresentante del fornitore, un rappresentante del punto vendita al dettaglio o un cliente. La funzione restituisce il nome e il cognome di un ContactID specifico e il tipo di contatto per la persona. L'espressione CASE nell'istruzione SET determina il valore da visualizzare per la colonna ContactType in base alla presenza della colonna ContactID nelle tabelle Employee, StoreContact, VendorContact o Individual (cliente).
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. Utilizzo di CASE in una clausola HAVING
Nell'esempio seguente viene utilizzata l'espressione CASE in una clausola HAVING per limitare le righe restituite dall'istruzione SELECT. L'istruzione restituisce la retribuzione oraria massima per ogni titolo professionale nella tabella HumanResources.Employee. La clausola HAVING limita i titoli professionali a quelli associati a uomini con una retribuzione massima maggiore di 40 dollari o a donne con una retribuzione massima maggiore di 42 dollari.
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;
Vedere anche