CASE expressions can be used in SQL anywhere an expression can be used. Example of where CASE expressions can be used include in the SELECT list, WHERE clauses, HAVING clauses, IN lists, DELETE and UPDATE statements, and inside of built-in functions.
Two basic formulations for CASE expression
1) Simple CASE expressions
A simple CASE expression checks one expression against multiple values. Within a SELECT statement, a simple CASE expression allows only an equality check; no other comparisons are made. A simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned.
Syntax:
CASE expression
WHEN expression1 THEN expression1
[[WHEN expression2 THEN expression2] [...]]
[ELSE expressionN]
END
Example:
DECLARE @TestVal INT
SET @TestVal = 3
SELECT
CASE @TestVal
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
ELSE 'Other'
END
2) Searched CASE expressions
A searched CASE expression allows comparison operators, and the use of AND and/or OR between each Boolean expression. The simple CASE expression checks only for equivalent values and can not contain Boolean expressions. The basic syntax for a searched CASE expressions is shown below:
Syntax:
CASE
WHEN Boolean_expression1 THEN expression1
[[WHEN Boolean_expression2 THEN expression2] [...]]
[ELSE expressionN]
END
Example:
DECLARE @TestVal INT
SET @TestVal = 5
SELECT
CASE
WHEN @TestVal <=3 THEN 'Top 3'
ELSE 'Other'
END
Reference : Pinal Dave (http://www.SQLAuthority.com)
pinaldave
j’ai utilisé la page, ça m’a bien servi, merci beaucoup
Hi,
Nice article , however, can you guide how we can manage the case statement when there are more then 600 options to select from.
Awais,
You could possibly Indent all the statements inside the Case Statement.
hi
m a newbie to SQL and trying to convert the following query to SQL
can u change this query to SQL update query using “case’ or “decode” by also providing me the detail explaination
UPDATE IMP_POSITIONS_NEW INNER JOIN POSITIONS
ON IMP_POSITIONS_NEW.JOBCODE = POSITIONS.POSITION_CODE
SET
POSITIONS.CUSTOM_MINIMUM = IIf([IMP_POSITIONS_NEW].[Grade]=’MBC’,[IMP_POSITIONS_NEW].[MRR_LOW],[IMP_POSITIONS_NEW].[MIN_RT_ANNUAL]),
POSITIONS.CUSTOM_MIDPOINT = IIf([IMP_POSITIONS_NEW].[Grade]=’MBC’,[IMP_POSITIONS_NEW].[MRP],[IMP_POSITIONS_NEW].[MID_RT_ANNUAL]), POSITIONS.CUSTOM_MAXIMUM = IIf([IMP_POSITIONS_NEW].[Grade]=’MBC’,[IMP_POSITIONS_NEW].[MRR_HIGH],[IMP_POSITIONS_NEW].[MAX_RT_ANNUAL]);
I am wondering if it is possible to do login in SQL Server like this
select *
FROM from employees
WHERE CASE WHEN a=1 THEN name = ‘Jim’ WHEN a=2 THEN EmployeeID = 90 WHEN a=4 THEN EmployeeStatus = ‘Fired’
can you see how im an trying to do a dynamic where clause is that possible with a case statement some how ?
hi,
is it possible to use a case statement with the IN clause? for example below, how to use case statement without using the ‘if’ statement
if @type = 1
select * from table1 where code in (’a',’b',’c')
else
select * from table1
thx.
BirdBuster, you can try this:
SELECT *
FROM employees
WHERE 1 = 1
and [name] = (CASE WHEN a=1 THEN ‘Jim’ else [name] end)
and EmployeeID = (case WHEN a=2 THEN 90 else EmployeeID end)
and EmployeeStatus = (case WHEN a=4 THEN ‘Fired’ else EmployeeStatus end)
Note that possible null values for name, EmployeeID, and EmployeeStatus are not handled for simplicity.
Tony W,
case function can be used with the IN clause, but it’s limited (the result expression can only return one value). In other words, something like the following (slightly modified version of your original) can be rewritten.
if @type = 1
select * from table1 where code in (’a’) –code=’a’ in essence
else
select * from table1
–rewritten using the case
select * from table1 where code in (case when @type = 1 then ‘a’ else code end)
So, to rewrite your original statement without the ‘if’, here’s an ugly version:
select * from table1 where
code in (case when @type = 1 then ‘a’ else code end)
or code in (case when @type = 1 then ‘b’ else code end)
or code in (case when @type = 1 then ‘c’ else code end)
But you cannot use the following:
select * from table1 where
code in (case when @type = 1 then (select ‘a’ union select ‘b’ union select ‘c’) else code end)
This will return the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.
Your article is greatly appreciated!!!!!!!!!!!
How do you include an update statement in a case statement xmlDocument…….
eg.
CASE WHEN (@xml.exist(’/ROOT/Trip/RouteItem[@RouteDBId="875077"]‘)) THEN
UPDATE route_details
SET
route_details.trip_authority_code = XMLRouteDetails.TripAuthorityNumber,
route_details.start_route_location_name = XMLRouteDetails.StartLocationName,
route_details.Start_Date = XMLRouteDetails.StartDate
From OpenXml(@intPointer,’/ROOT/Trip/RouteItem’,3)
With
(
TripAuthorityNumber int ‘../@TripAuthorityNumber’,
StartLocationName varchar(100),
StartDate smalldatetime
XMLRouteDetails WHERE route_details.trip_authority_code = XMLRouteDetails.TripAuthorityNumber
– Rollback the transaction if there were any errors
IF @@ERROR 0
BEGIN
– Rollback the transaction
ROLLBACK
– Raise an error and return
RAISERROR (’Error in updating route in route_details table’, 16, 1)
RETURN
END
ELSE
INSERT INTO route_details
(
trip_authority_code,
start_route_location_name,
Start_Date
)
Select
@TripID,
StartLocationName,
StartDate
from OpenXml(@intPointer,’/ROOT/Trip/RouteItem’,3)
With
(
TripAuthorityNumber int ‘../@TripAuthorityNumber’,
StartLocationName varchar(100),
StartDate smalldatetime
)
– Rollback the transaction if there were any errors
IF @@ERROR 0
BEGIN
– Rollback the transaction
ROLLBACK
– Raise an error and return
RAISERROR (’Error in inserting route into route_details table’, 16, 1)
RETURN
END
END
COMMIT
exec sp_xml_removedocument @intPointer
RETURN
I am passing two different parameters 1>@Name 2>@Id. Depending on parameter @Name I want to fire select statement on different table. Can I put this in case statement,if yes then how?
for eg:
@name= text1
@Id=87
select col1 from tab1 where id=@Id
@name=text2
@Id=85
select col2 from tab2 where id=@Id
Thanks in advance.
Not working ,
SELECT
case when (ResheduleDate = ”) then AppDate else ResheduleDate end as FinalDate
from tbl_Appointments
WHERE ProviderID = 76 and Status = 2 Group by FinalDate
Thanks
I am trying to use a 2 case statements inside one table. both statements work but they dont work together, any ideas??
Sunil ( with dollar sign)
Please write your query here….
Thanks,
IM