SQL SERVER – CASE Statement/Expression Examples and Explanation

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)

,
Previous Post
SQL SERVER – Fix : Error: 18452 Login failed for user ‘(null)’. The user is not associated with a trusted SQL Server connection.
Next Post
SQL SERVER – 64 bit Architecture and White Paper

Related Posts

149 Comments. Leave new

  • 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
  • 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
    • 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
  • Thank you Madhivanan :)

    Reply
  • I want to implement this condition in sql

    case when @LOB=’ABC’ then LOB=’ABC’ else LOB’ABC’ end

    Reply
  • 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
  • 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
  • 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
  • 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
  • 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
    • 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
  • 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
  • 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
  • You can find more examples on CASE expression here at

    Reply
  • 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
  • can we use function in then part of case when ?

    Reply
  • 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
  • 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

Leave a Reply

Menu