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
hi,
Wanted to know if we can use “IN” in case statement like
case @variable
when in (1,2,3) then
when in (4,5,6) then…
Thanks,
Nirav
Hi,
That’s almost correct. Try this:
CASE
WHEN @variable IN (1,2,3) THEN ..
WHEN @variable IN (4,5,6) THEN ..
END
Thanks Polelo
Hi Experts,
I have a table PRD with the following value:
ComCod WHLOC
1 Null
2 Null
2 SLC1
I wish to update
1. WHLOC with “SPL” where comcod = ‘1’ and WHLOC is empty.
2. WHLOC with “SCM” where comcod =’2″ and WHLOC is empty.
I wrote the following case statement but it is invalid.
UPDATE prd SET WHLoc =(CASE WHEN (ComCod = ‘1’ and ISNULL(whloc,”)=” THEN ‘SPL’ )
CASE WHEN (ComCod = ‘2’ and ISNULL(whloc,”)=”) THEN ‘SCM’ ELSE END )
Thanks in advance for any help/advice!
@Andrea
How’s this?
UPDATE
prd
SET
WHLoc = CASE comcod
WHEN 1 THEN ‘SPL’
WHEN 2 THEN ‘SCM’
END
WHERE
whloc IS NULL;
Hi Brian,
Thanks for your help!
Your advice works great for my problem!
rgds/Andrea
Hello,I am a 13 year old boy,Visual Basic is in my sylabbus,Please contact me on chitu_smarty@yahoo.co.in
Hi is it posible to write something like
Case
when name in (‘john’, ‘ben’, ‘david’) then male else female end
thanks in advance
@Thato
Yes. But male male and female should also be in quotes.
DECLARE @heads VARCHAR(MAX)
select
case when div.Name = ‘Europe’ THEN
(SELECT COALESCE(@heads+ ‘ , ‘,”) + dbo.PersonName(em1.EmpID) from
(
/Complex Select stat to get
) as em1) else ” END as EUROPE
from Division div
I get error while using COALESCE expression in THEN clause of WHEN in CASE.
can you tell me what am I missing?
You need to use IF clause instead of CASE Expression
use[ramesh]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author:
— Create date:
— Description:
— =============================================
CREATE PROCEDURE SP_Emp
@salary int
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
Update employee set salary =
CASE
WHEN @salary =1000
salary+100
Else
Salary+10;
END
Above code i am getting problem
Msg 102, Level 15, State 1, Procedure SP_Emp, Line 17
Incorrect syntax near ‘salary’.
Hello Ramesh,
You are missing something here:
WHEN @salary =1000
salary+100
Else
Salary+10;
I think you want to write it as below:
WHEN @salary =1000 THEN salary+100
Else Salary+10;
Regards,
Pinal Dave
Update employee set salary =salary+
CASE
WHEN @salary =1000
100
Else
10
End;
Hellow Pinal Dave can we use CASE WHEN in Stored procedure? please explain with one example. i have written one code but i am getting error
My Code is:
CREATE procedure sp_Products
(
@ProductId nvarchar(max),
@Catalog nvarchar(100)
)
as
begin
case @Catalog
when ‘Courseware’
then select * from tblProducts where ProductId in(@ProductId)
when ‘Options’
then select * from tblorder where ProductId in(@ProductId)
end
end
CASE expression wouldn’t work that way
Use IF statement
CREATE procedure sp_Products
(
@ProductId nvarchar(max),
@Catalog nvarchar(100)
)
as
begin
if @Catalog='Courseware'
select * from tblProducts where ProductId in(@ProductId)
else if @Catalog='Options'
select * from tblorder where ProductId in(@ProductId)
end
ThanQ Madhivanan
i have 8 column in my table and someone contain 0 and another contain value
i want to check smaller value between all rows and display it in another column
what i do for exclude column that contain zero
plz help me
Post some sample data with expected result
Try This one it may work
select min(YourColumnName) as MinimuValue from tablename where YourColumnName > 0
I need to add an additional statement to include :
CASE WHEN mtmatter IN (SELECT
from matter, mattimhs
where mclient = ‘99991’
and matter.mmatter = mattimhs.mtmatter
Is there a way to combine this with the following statement?
ALTER VIEW Non_Billable_Breakdown AS
SELECT mttk AS nbb_tk,
mtper AS nbb_per,
mthrnbdw AS nbb_tothr,
CASE WHEN mtmatter = ‘09999-006600’ THEN mthrnbdw ELSE 0 END AS nbb_admin,
CASE WHEN mtmatter = ‘09999-006605’ THEN mthrnbdw ELSE 0 END AS nbb_comply,
CASE WHEN mtmatter = ‘09999-006610’ THEN mthrnbdw ELSE 0 END AS nbb_commact,
CASE WHEN mtmatter = ‘09999-006620’ THEN mthrnbdw ELSE 0 END AS nbb_educ,
CASE WHEN mtmatter = ‘09999-006630’ THEN mthrnbdw ELSE 0 END AS nbb_clidev,
CASE WHEN mtmatter = ‘09999-006631’ THEN mthrnbdw ELSE 0 END AS nbb_emplawconf,
CASE WHEN mtmatter = ‘09999-006640’ THEN mthrnbdw ELSE 0 END AS nbb_probono,
CASE WHEN mtmatter = ‘09999-006642’ THEN mthrnbdw ELSE 0 END AS nbb_general,
CASE WHEN mtmatter = ‘09999-006650’ THEN mthrnbdw ELSE 0 END AS nbb_personal,
CASE WHEN mtmatter = ‘09999-006660’ THEN mthrnbdw ELSE 0 END AS nbb_lobby,
CASE WHEN mtmatter = ‘09999-006680’ THEN mthrnbdw ELSE 0 END AS nbb_legalsvcs,
CASE WHEN mtmatter = ‘09999-080920’ THEN mthrnbdw ELSE 0 END AS nbb_genimm,
CASE WHEN mtmatter = ‘09999-006606’ THEN mthrnbdw ELSE 0 END AS nbb_profact,
CASE WHEN mtmatter = ‘09999-006607’ THEN mthrnbdw ELSE 0 END AS nbb_recruitting,
CASE WHEN mtmatter = ‘09999-006615’ THEN mthrnbdw ELSE 0 END AS nbb_iGreenLaw,
CASE WHEN mtmatter = ‘09999-006618’ THEN mthrnbdw ELSE 0 END AS nbb_economicclarity
FROM mattimhs
@Debbie
Now sure what you want, so i’ll guess. I think it can be EXISTS. Also, all those CASE statements can use the simple-expression. It’s slightly clearer.
SELECT
mttk nbb_tk,
mtper nbb_per,
mthrnbdw nbb_tothr,
CASE mtmatter WHEN ‘09999-006600’ THEN mthrnbdw ELSE 0 END nbb_admin,
CASE mtmatter WHEN ‘09999-006605’ THEN mthrnbdw ELSE 0 END nbb_comply,
CASE mtmatter WHEN ‘09999-006610’ THEN mthrnbdw ELSE 0 END nbb_commact,
CASE mtmatter WHEN ‘09999-006620’ THEN mthrnbdw ELSE 0 END nbb_educ,
CASE mtmatter WHEN ‘09999-006630’ THEN mthrnbdw ELSE 0 END nbb_clidev,
CASE mtmatter WHEN ‘09999-006631’ THEN mthrnbdw ELSE 0 END nbb_emplawconf,
CASE mtmatter WHEN ‘09999-006640’ THEN mthrnbdw ELSE 0 END nbb_probono,
CASE mtmatter WHEN ‘09999-006642’ THEN mthrnbdw ELSE 0 END nbb_general,
CASE mtmatter WHEN ‘09999-006650’ THEN mthrnbdw ELSE 0 END nbb_personal,
CASE mtmatter WHEN ‘09999-006660’ THEN mthrnbdw ELSE 0 END nbb_lobby,
CASE mtmatter WHEN ‘09999-006680’ THEN mthrnbdw ELSE 0 END nbb_legalsvcs,
CASE mtmatter WHEN ‘09999-080920’ THEN mthrnbdw ELSE 0 END nbb_genimm,
CASE mtmatter WHEN ‘09999-006606’ THEN mthrnbdw ELSE 0 END nbb_profact,
CASE mtmatter WHEN ‘09999-006607’ THEN mthrnbdw ELSE 0 END nbb_recruitting,
CASE mtmatter WHEN ‘09999-006615’ THEN mthrnbdw ELSE 0 END nbb_iGreenLaw,
CASE mtmatter WHEN ‘09999-006618’ THEN mthrnbdw ELSE 0 END nbb_economicclarity,
CASE WHEN EXISTS(SELECT * FROM matter WHERE mclient = ‘99991’ and matter.mmatter = mattimhs.mtmatter)
THEN 1 ELSE 0
END
FROM
mattimhs;
select DonationId,KinName,DonationType,
CASE DonationType
WHEN 1 THEN (SELECT VDNo FROM Exchange_VD WHERE DonationId=’aa’)
WHEN 3 THEN (select PatientId from View_PatientInfo VP INNER JOIN Billing_Master BM ON BM.RequestId=VP.RequestId INNER JOIN
Exchange_POR ER ON ER.IssueId=BM.Id WHERE ER.PORNo=’POR1011/1/3′)
ELSE ‘no’
END
from View_DonorInformation
This wont work if the subquery returns more than one row
I have a problem to insert data in a table. when two user on diffrent site want to insert data into same table, error thrown by system.
Another Problem is that : I fatch Max(columnName) and max value+1 my next value for insert statement , Now point is that when two user aceess same max value and insert same value into table than a confliction or violation of primary key error thrown by software.
plz plz plz solve these problem. And thanx in advance…
You need to make use of a trasaction to avoid the error. Aslo just before adding data to table query for max(col)+1
select dm01_cust,dm01_site,dm01_date,DM01_INVC,DM01_SSIT,DM01_PBLE,DM01_BILLINDV,DM01_CONTRACT,DM01_AMOUNT,
(case
when dm05a_link is not null then
‘Service ‘+cast(DM05A_AMNT as varchar(18))
when dm05b_link is not null then
‘Service ‘+cast(DM05b_AMNT as varchar(18))
when dm05l_link IS not null then
‘Service ‘+cast(DM05l_AMNT as varchar(18))
when dm05z_link IS not null then
‘Service ‘+cast(DM05Z_AMNT as varchar(18))
else 0 end)
–,COUNT(*)
from TRUX_BV_COMP.dbo.dm01
left join TRUX_BV_COMP.dbo.dm05a
on DM01_ID=dm05a_link
left join TRUX_BV_COMP.dbo.dm05b
on DM01_ID=dm05b_link
left join TRUX_BV_COMP.dbo.dm05l
on DM01_ID=dm05l_link
left join TRUX_BV_COMP.dbo.dm05z
on DM01_ID=dm05z_link
where DM01_DATE=’2004-08-31’
and DM01_CUST=9460
conversion failed when converting the varchar value to data type int
Nice, but explanation is too short and not describe complex queries.
need to explain some complex queries using case.
Refer this post to know more examples about the CASE expression
I have a scenario where I want to assign the value of column c2 to a seperate variable based on the value of column c1 in the same query. So if the value of c1 is 1, then the value of c2 should go be assigned to v1, if the value of c1 is 2, then the value of c2 should be assigned to variable v2 and so on. I tried the below query, but it gives a syntax error. Any help is appreciated.
SELECT CASE WHEN CURRENCY_CODE = ‘USD’ THEN @USD_sec_alias = SECURITY_ALIAS
WHEN CURRENCY_CODE = ‘GBP’ THEN @EUR_sec_alias = SECURITY_ALIAS
WHEN CURRENCY_CODE = ‘EUR’ THEN @EUR_sec_alias = SECURITY_ALIAS
END
FROM SECURITY.DBO.SECURITY_MASTER
WHERE CURRENCY_CODE IN (‘USD’,’GBP’,’EUR’)
AND INVESTMENT_TYPE = ‘CASH’
Thank you,
Chintan Somaiya
I’m trying to create a case statement in the Where Clause, but I keep getting an eror in the statement.
What I’m trying to do is Run it so that if it is pulling after a specific date to filter out sppecific Codes:
Select *
From Table as a
Where
Code = Case when Date > ’10-02-2010′ then Code Not in (1,2,3,4) Else Code not in(5,6,7,8) End.
I keep getting the following error:
Incorrect syntax near the keyword ‘in’.
Any thoughts?
Try this
Select *
From Table as a
Where
(Code Not in (1,2,3,4) and Date > ’10-02-2010′ )
OR
(Date <- ’10-02-2010′ and Code not in(5,6,7,8))
i mtrying to convert the nvarchar datatype in to numeric.
but it showing error.
“Error converting data type nvarchar to numeric.”
tell me how to convert this.
Post the code you used
Hi,
Would you be able to help me in an SQL query? The process is currently performing the following query.
SELECT A,B,C,D
FROM TableA
WHERE Date_First_Invoiced_AR NOT BETWEEN MM/DD/YY AND MM/DD/YY
I need to change it so that The Date_First_Invoiced_AR is from TableB, if it exists, and if not then use TableA.
I tried the following, but it did not work.
SELECT A,B,C,D,
CASE WHEN TableB.Date_First_Invoiced ” THEN TableB.Date_First_Invoiced_AR ELSE TableA.Date_First_Invoiced_AR END AS DateInvoiced
FROM TableA
LEFT OUTER JOIN TableB ON TableA.ID = TableB.ID
WHERE DateInvoiced NOT BETWEEN MM/DD/YY AND MM/DD/YY
I get an error message ‘Invalid column name ‘DateInvoiced’.
Any help would be GREATLY appreciated!!
Hi pinal.
could u please help mi on this query.
SELECT e.Name +’ ‘ + e.Surname as Name
,count ([ID_EmployeeTask]) as workinkDays
,sum ([FinalWT]) as PeriodTotalWT
,sum ([FinalPoints]) as PeriodTotalpoints
,sum ([FinalWT])/count ([ID_EmployeeTask]) as AverageWT
,sum ([FinalPoints])/count ([ID_EmployeeTask]) as ProductivityPerPeriod
,’ ProductivityperHour’ =(case when sum ([FinalWT])= 0 then 0 else sum ([FinalPoints])/sum ([FinalWT])End)
FROM [OPS_ReportingDB].[Organization].employee e
inner join [OPS_ReportingDB].[TaskData].[EmployeeTask]
on [OPS_ReportingDB].[TaskData].[EmployeeTask].[ID_Employee] = e.[ID_Employee]
where (e.id_Employee in –(select e.id_Employee from Organization.Employee e inner join Organization.Unit u on e.ID_Unit =u.ID_Unit inner join Organization.Department D on u.ID_Department= D.ID_Department inner join Organization.Division I on D.ID_Division =I.ID_Division and ID_HeadOfDivision = 2 ))
(case
when 52 in (select ID_TeamLeader from [OPS_ReportingDB].Organization.Unit)
then (select e.id_Employee from Organization.Employee e inner join Organization.Unit u on e.ID_Unit =u.ID_Unit and ID_TeamLeader = 52)
when 52 in (select ID_HeadOfDepartment from [OPS_ReportingDB].Organization.Department)
then (select e.id_Employee from Organization.Employee e inner join Organization.Unit u on e.ID_Unit =u.ID_Unit inner join Organization.Department D on u.ID_Department= D.ID_Department and ID_HeadOfDepartment = 52 )
when 52 in (select ID_HeadOfDivision from [OPS_ReportingDB].Organization.Division)
then (select e.id_Employee from Organization.Employee e inner join Organization.Unit u on e.ID_Unit =u.ID_Unit inner join Organization.Department D on u.ID_Department= D.ID_Department inner join Organization.Division I on D.ID_Division =I.ID_Division and ID_HeadOfDivision = 52 )
else (0) end) )
group by e.[ID_Employee] ,e.Name , e.Surname order by Name
regards
You need to post what the problem with your query