SQL SERVER – CASE Statement/Expression Examples and Explanation

April 14, 2007

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)

Best Practices, SQL Scripts

SQL SERVER – Fix : Error: 18452 Login failed for user ‘(null)’. The user is not associated with a trusted SQL Server connection.

SQL SERVER – 64 bit Architecture and White Paper

149 Comments. Leave new

balaji.p
October 24, 2012 9:01 pm
How to use save points in a loop. Say for example I have 100 records and Iam processes records in batches of 10. when Iam processing 15 record I get a error. but at this state I need to commit 11 to 14 record. Can you please help me in providing a solution
Reply
Harinath
November 19, 2012 5:04 pm
Hi,
I want to use case statement in having clause. Is below correct?
GROUP BY D.[LOGIN],D.[BOOKED DATE]
HAVING (CASE WHEN Datepart(Weekday, D.[BOOKED DATE])=4 THEN SUM(D.[EFFORT]) < 510 ELSE SUM(D.[EFFORT]) < 480 END)
I want to show the records, whose sum is <510 on weekdays and sum<480 on 4th day of the week.
Can anyone please suggest, how to write above query.
Reply
- madhivanan
  November 20, 2012 11:51 am
  Try
  GROUP BY D.[LOGIN],D.[BOOKED DATE], Datepart(Weekday, D.[BOOKED DATE])
  HAVING Datepart(Weekday, D.[BOOKED DATE])=4 AND SUM(D.[EFFORT]) < 510 OR Datepart(Weekday, D.[BOOKED DATE])4 AND SUM(D.[EFFORT]) < 480
  Reply
Harniath
November 29, 2012 12:54 pm
Thank you Madhivanan :)
Reply
Johnson
December 3, 2012 9:25 pm
I want to implement this condition in sql
case when @LOB=’ABC’ then LOB=’ABC’ else LOB’ABC’ end
Reply
ariel
January 29, 2013 11:24 am
Create PROCEDURE [dbo].[spRefreshIndex]
@tblName varchar(100)
,@varOutPut VARCHAR(100) OUTPUT
,@scr varchar(2000)
AS
BEGIN
SELECT @scr = CASE @tblName
WHEN TBL_ACCREDITED_COMPANY THEN ‘ALTER FULLTEXT INDEX ON TBL_ACCREDITED_COMPANY START FULL POPULATION’
END
END
please help..
i have an error of invalid column name
tnx
Reply
teju
February 7, 2013 4:42 pm
select e.AC_NO || e.PART_NO || s.SECTION_NO || s.Section_name_Hi || s.Section_Name_En || a.DIST_NO || d.DIST_NAME || d.DIST_NAME_E || p.Policest_Name || p.Policest_Name_En || su.SubDivision_ID || t.Tahsil_Name ||t.Tahsil_Name_En as address
from EPICDB.dbo.EPIC as e inner join AC_008.dbo.sec_detail as s
on s.PART_NO = e.Part_No inner join erollcontrol.dbo.AC_List as a
on e.AC_NO = a. AC_NO inner join EPICDB.dbo.DISTRICT as d
on d.DIST_NO = a.DIST_NO inner join erollcontrol.dbo.Policest as p
on s.CCode = p. ccode inner join erollcontrol.dbo.SubDivisions as su
on s.CCode = su.ccode inner join erollcontrol.dbo.Tahsils as t
on su.ccode = t.ccode order by AC_NO
hi,
here am porting valuing from multiple databases multiple tables and i want to port all these values into destination table EPIC for the field address.. i m getting output wth different columns but i want to concatenate all the values and send to address field..
Reply
Aman
July 10, 2013 11:33 pm
nice example
Reply
mitesh vaishnav
July 20, 2013 12:45 pm
my data is like below……..
table name : mytable
column name : f1obt
data
7.5
9.5
ML
sql query : select case when f1obt=’ML’ then f1obt else CONVERT(float,f1obt)*4 end from mytable
i also try
select case when f1obt=’ML’ then ‘ML’ else CONVERT(float,f1obt)*4 end from mytable
when i write query like below it thrown error….error converting varchar to float
Reply
asdf
August 5, 2013 3:54 pm
I have two user in a table one is createdby and another is modifiedby.
and i passed the value @User to the procedure
i want to select all the values from the table like
select * from tablename where (if modifiedby is not null then modifiedby=@user else createdby=@user).Kindly help me how to do this.
Reply
Kashif Iqbal
August 17, 2013 4:44 pm
I am facing a problem. I have a query where i m using case statement. I have two condition/ Nested Condition.
If one condition getting true then second one must be check… if thats ture also then want to do some task, but with case statement i didn’t achieve … I am placing the query as well.
SELECT Registration.PatientName
, CASE Registration.PaymentTypeCode
WHEN 4 THEN 2
WHEN 9 THEN 2
WHEN 101 THEN 2
WHEN 103 THEN 2
WHEN 10 THEN
CASE Registration.PaymentTypeCode
WHEN (CAST(DATEDIFF(Minute, Registration.LastRegistrationDate, GETDATE())AS INT) > 1440)
THEN 2
END
ELSE Registration.PaymentTypeCode
END PaymentTypeCode
FROM Registration
Where PCN Like ‘000000’
It gives an error: Line 9: Incorrect syntax near ‘>’.
Reply
- madhivanan
  September 19, 2015 1:39 pm
  Try this
  SELECT Registration.PatientName
  , CASE Registration.PaymentTypeCode
  WHEN 4 THEN 2
  WHEN 9 THEN 2
  WHEN 101 THEN 2
  WHEN 103 THEN 2
  WHEN 10 THEN
  CASE
  WHEN (CAST(DATEDIFF(Minute, Registration.LastRegistrationDate, GETDATE())AS INT) > 1440)
  THEN 2
  END
  ELSE Registration.PaymentTypeCode
  END PaymentTypeCode
  FROM Registration
  Where PCN Like ‘000000’
  Reply
newOne
February 25, 2014 1:57 pm
Hi Pinal,
I got into an issue with the below statement. I don’t understand why it is not working as expected. Structure for table is below
Months Cash
1 4.13
2 46.02
3 46.02
4 5.31
5 5.31
6 51.33
7 393.53
8 393.53
9 46.02
10 51.33
11 57.82
12 32.45
13 0
14 0
15 0
select ‘Total’ as Months
,Cast(max(case when Cash = ‘4.13’ then Cash end) as varchar(20)) [1]
,Cast(max(case when Cash = ‘46.02’ then Cash end) as varchar(20)) [2]
,Cast(max(case when Cash = ‘46.02’ then Cash end) as varchar(20)) [3]
,Cast(max(case when Cash = ‘5.31’ then Cash end) as varchar(20)) [4]
,Cast(max(case when Cash = ‘5.31’ then Cash end) as varchar(20)) [5]
,Cast(max(case when Cash = ‘51.33’ then Cash end) as varchar(20)) [6]
,Cast(max(case when Cash = ‘393.53’ then Cash end) as varchar(20)) [7]
,Cast(max(case when Cash = ‘393.53’ then Cash end) as varchar(20)) [8]
,Cast(max(case when Cash = ‘46.02’ then Cash end) as varchar(20)) [9]
,Cast(max(case when Cash = ‘51.33’ then Cash end) as varchar(20)) [10]
,Cast(max(case when Cash = ‘57.82’ then Cash end) as varchar(20)) [11]
,Cast(max(case when Cash = ‘32.45’ then Cash end) as varchar(20)) [12]
,Cast(max(case when Cash = ‘0’ then Cash end) as varchar(20)) [13]
,Cast(max(case when Cash = ‘0’ then Cash end) as varchar(20)) [14]
,Cast(max(case when Cash = ‘0’ then Cash end) as varchar(20)) [15]
from #Cash
MonthNumber 1 2 3 4 5 6 7
Total 4.13 NULL NULL NULL NULL 51.33 NULL
8 9 10 11 12 13 14 15
NULL NULL 51.33 NULL 32.45 0 0 0
Why there is null in the output? how can we resolve it?
Reply
- newOne
  February 26, 2014 4:35 pm
  Can anybody have a look at this strange problem?
  Reply
Priya
May 12, 2014 3:07 pm
Hi,
I am having the following queries, in which “TOTAL_AMOUNT” is decimal data type and “INVOICE_NO” is varchar data type.
First Query working correctly. but Second Query results in error as, “Error converting data type varchar to numeric.”.
if i convert the “TOTAL_AMOUNT” as varchar, it sorted like nvarchar. But i need to sort as decimal. Help me to get the correct solution.
1) SELECT
*
FROM
TRN_INVOICE
ORDER BY
CASE ‘1’
WHEN ‘1’ THEN TOTAL_AMOUNT
WHEN ‘2’ THEN INVOICE_NO
END
2) SELECT
*
FROM
TRN_INVOICE
ORDER BY
CASE ‘2’
WHEN ‘1’ THEN TOTAL_AMOUNT
WHEN ‘2’ THEN INVOICE_NO
END
Reply
madhivanan
August 19, 2015 3:35 pm
You can find more examples on CASE expression here at
Reply
Sathya
September 18, 2015 4:58 pm
hi ,
I have 2 tables tblCallActivity and tblTimesheet.In tblCallActitvity i have ID,CaseID,CallStatus,HOremark,UpdatedBy,UpdatedOn.In tblTimesheet i have TimesheetID,Timesheetin,Timesheetout,EEmployeeID,CaseId,Allocatedcallstatus and Allotedon.Here it should Allot Scheduler for Tbltimesheet caseid and It should allot Normal Update for tblCallActivity here i am using case for this query but its not working it display Only Normal Update here is my query.
SELECT
CA.CaseID,
CA.UpdatedOn,
CASE WHEN
(
SELECT TimesheetId
FROM tblTimesheet
WHERE CaseId = CA.CaseID
) > 0 THEN ‘NormalUpdate’
ELSE ‘Scheduler’
END
FROM tblCallActivity CA where CallStatus=’ALLOTED’ Can any plz help?
Reply
kamran
May 12, 2016 3:14 pm
can we use function in then part of case when ?
Reply
Iynapudi
May 20, 2016 7:24 pm
Hi All,
I am using below query to convert multiple rows in to single row with extra columns and this works perfectly fine but i have another table with huge number of data and unable to write case statement. Is there any possibility to pass the value dynamically. Please suggest
select WirelessNumber,
max( case when ChargeGroup = ‘Message Plan’ then ChargeDesc else ” end) ‘Message Plan’,
max( case when ChargeGroup = ‘Data Plan’ then ChargeDesc else ” end) ‘Data Plan’,
max( case when ChargeGroup = ‘Voice Plan’ then ChargeDesc else ” end) ‘Voice Plan’,
max( case when ChargeGroup = ‘Global Plan’ then ChargeDesc else ” end) ‘Global Plan’
from tbl_Test_Inventory
group by WirelessNumber
Reply
- Iynapudi
  May 24, 2016 5:56 pm
  Please update on the above query
  Reply
Khadija Dev Ablam
December 20, 2016 10:03 pm
with the following example i’m getting errror ‘Incorrect syntax near the keyword ‘SET’. ‘
SELECT
CASE
WHEN @Famille is null and @Article is not null and @Client is null and @Fournisseur is null and @Magasin is null and @Commercial is null THEN
set @qteFC = (select SUM([INV1].[Quantity]) from [INV1] where [INV1].[ItemCode] = @Article)
set @TotalFC = (select SUM([INV1].[LineTotal]) from [INV1] where [INV1].[ItemCode] = @Article)
set @qteAC = (select SUM([RIN1].[Quantity]) from [RIN1] where [RIN1].[ItemCode] = @Article)
set @TotalAC = (select SUM([RIN1].[LineTotal]) from [RIN1] where [RIN1].[ItemCode] = @Article)
set @qteFF = (select SUM([PCH1].[Quantity]) from [PCH1] where [PCH1].[ItemCode] = @Article)
set @TotalFF = (select SUM([PCH1].[LineTotal]) from [PCH1] where [PCH1].[ItemCode] = @Article)
set @qteRF =(select SUM([PDN1].[Quantity]) from [PDN1] where [PDN1].[ItemCode] = @Article and [PDN1].[DocEntry] NOT IN (SELECT [BaseRef] FROM [PCH1]))
set @TotalRF =(select SUM([PDN1].[LineTotal]) from [PDN1] where [PDN1].[ItemCode] = @Article and [PDN1].[DocEntry] NOT IN (SELECT [BaseRef] FROM [PCH1]))
set @qteAF = (select SUM([RPC1].[Quantity]) from [RPC1] where [RPC1].[ItemCode] = @Article)
set @TotalAF = (select SUM([RPC1].[LineTotal]) from [RPC1] where [RPC1].[ItemCode] = @Article)
set @CA = isnull(@TotalFC, 0) – isnull(@TotalAC, 0)
set @CoutA = isnull(@TotalFF, 0) +isnull(@TotalRF, 0)- isnull(@TotalAF, 0)
set @MB = @CA – @CoutA
set @MBPrcn = isnull((@MB /NULLIF( @CA,0)) * 100,0)
select isnull(@qteFC, 0) – isnull(@qteAC, 0) “Quantite Vendue”, isnull(@qteFF, 0) + isnull(@qteRF, 0) – isnull(@qteAF, 0) “Quantite Achete”, @CA “Chiffre d’affaire”, @CoutA “Cout d’achat”, @MB “Marge brut”, @MBPrcn “% Marge Brute”
end
sir what do u advice me to do cuze i can’t use if else statement
Reply
- Pinal Dave
  December 24, 2016 3:59 pm
  if you can post simple version of repro, I am sure others can help.
  Reply