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://blog.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.
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.
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
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?
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
[...] SQL SERVER – CASE Statement/Expression Examples and Explanation [...]
informative article
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
@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
I am attempting to update table1.status with a 1 if it exists within table2 otherwise i want it to update with a 0 if it does not. the statement I have is as follows
Update
[table1]
SET Status = (
case
when
(Select status from table1 inner join table2
on table1.ID = table2.ID)
then ‘1′
else ‘0′
I am getting problem in removing special character from string
I am doing like this :I want to do multiple sepcial charcter remove from this but i am able to replace only on charcter
declare @name varchar(10)
declare @currentchar varchar(10)
declare @backUp varchar(10)
declare @len int
declare @i int
set @name=’;kri;pal’
set @len=len(@name)
set @i=1
declare @Kk varchar(20)
WHILE @i <= @len
BEGIN
SET @currentchar = SUBSTRING(@name,@i, 1)
select
@backUp =(case when (@currentchar=';' )then
replace(@currentchar,';' ,'')
else
@currentchar end)
set @kk=isnull(@kk,'')+@backUp
SET @i = @i + 1
END
pls help me urgent:
@krish
Why not use REPLACE() for the entire string? No WHILE loop or anything, just a simple REPLACE(@name, ‘;’, ”)
Sub: CASE Statement in SQL server
Can you please tell me that
CASE Statement working with String / Varchar
means i want to check some varchar values
DECLARE @TestVal varchar
SET @TestVal = ‘Asma’
SELECT
CASE
WHEN @TestVal =’Asma’ THEN ‘Top 1′
ELSE ‘Other’
END
@Asma,
Ofcourse it will work.
Tejas
@Asma Qureshi
What is the problem?
Also, you can use a simple case expression:
DECLARE @TestVal varchar
SET @TestVal = ‘Asma’
SELECT
CASE @TestVal
WHEN ‘Asma’ THEN ‘Top 1′
ELSE ‘Other’
END