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 (https://blog.sqlauthority.com)
149 Comments. Leave new
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.
in that case,,looping can be the better option,,what do I think..
You can have all 600 values in a separate table and then join with that table
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 ?
You need to use this logic
select *
FROM from employees
WHERE
(a=1 and name = 'Jim') or
(a=2 and EmployeeID = 90) or
(a=4 and EmployeeStatus = 'Fired')
You can find more examples on CASE expression here at
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.
Use this
select * from table1
where ((code in ('a','b','c') and @type=1) or @type1)
My above reply should be
Use this
select * from table1
where ((code in ('a','b','c') and @type=1) or @type11)
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.
Thanks Prabhakar, your sample code lead me to the right track faced with a similar situation. Can you give me a quick example of how you would handle the NULL values in this situation?
Thanks…
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.
No. You need to use IF clause for this
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
What did you mean by not working?
Use NULL in place of ”
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
I need a return of two values from a case statement as here i am searching for a value in database. when a perticular value is found i need 2 vaues in my result set.
Pls suggest?
CASE would return only one value
Post your actual requirement
I have the following CASE Statement . I did it manually. how can I make it dynamic ?
CASE TourismFilmHeader.RefLanguageID
WHEN ‘1’ then (SELECT TourismLanguageMaster.Language FROM TourismLanguageMaster WHERE Language = ‘Arabic’)
WHEN ‘2’ then (SELECT TourismLanguageMaster.Language FROM TourismLanguageMaster WHERE Language = ‘English’)
WHEN ‘3’ then (SELECT TourismLanguageMaster.Language FROM TourismLanguageMaster WHERE Language = ‘Hindi’)
ELSE ‘None’
END
informative article
hi
Pls give me sql 2008 Note
hi chalana send me ur number i will give u note
Hi,
How can we do this
Select id, name,
CASE
WHEN AGE+@SomeVariable<17 THEN ‘Young’
ELSE AGE END
from users
I am trying to run case on a computed field and want to show a text incase the output of that field meets the criteria or simply echo the output.
Cannot use the field computation again, as it includes a subquery, which I don’t think should run again for performance reasons.
Thanks
Use derived table
select * from
(
Select id, name,
CASE
WHEN AGE+@SomeVariable<17 THEN ‘Young’
ELSE AGE END as AGE
from users
) as t
where AGE='some condition'
Also it is not good practice to convert numbers to text
@Hitendra
Can you rephrase your question please.
I could not understand your question.
If you want to pass column name as parameter, then you can do that using dynamic SQL.
Before I suggest you something, I want to make sure I understand your question. Please put your question in very clear form.
Thanks.
~ IM.
How about this?
Select id, name
CASE WHEN AGE+@SomeVariable<17 THEN ‘Young’
ELSE cast(AGE as varchar(5)) END as NewAge
from users
Where NewAge would return a string. or:
Select id, name, Age,
CASE WHEN AGE+@SomeVariable<17 THEN ‘Young’
ELSE ” END as SubjectiveAge
from users
Where Age would contain the age, and SubjectiveAge would contain ‘Young’ or an empty string.
Nice article. Can you pls tell me how to insert a null value in the database while mentioning the field name in the inser query.
i am getting value from textBox of non-required field user may not key-in any value.
pls guide me the way to write a stored procedure to come across this issue
Just use NULL