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

  • Could you guys help with this one?

    In the case statement below i an getting only data for the first two case statement, and the last 3 statements are suppressed.

    But when i put the second case statement in the end(i.e ‘Total FEW’) i am getting value for the first four condition and only the ‘Total FEW’ condition is suppressed.

    Pls give a solution!

    Case
    When T2.A = ‘CANADA’ and T2.B in (‘TRANSACTION FRAUD’, ‘IDENTITY FRAUD’, ‘INVESTIGATIONS’) Then ‘Canada’
    When T2.B ‘OTHER’ Then ‘Total FEW’
    When T2.A in (‘BANK CARD EAST’, ‘BANK CARD WEST’, ‘CANADA’, ‘CHOICE’, ‘DEBIT’, ‘DEBIT EAST’, ‘DEBIT WEST’, ‘OTHER’) and T2.B in (‘SMITH BARNEY’, ‘SPECIALTY’, ‘BOARDROOM’, ‘DOSS’) Then ‘DOSS’
    When T2.A in (‘BANK CARD EAST’, ‘BANK CARD WEST’, ‘CHOICE’, ‘CANADA’, ‘OTHER’) and T2.B = ‘IDENTITY FRAUD’ Then ‘ID Fraud’
    When T2.A in (‘BANK CARD EAST’, ‘BANK CARD WEST’, ‘CHOICE’, ‘OTHER’) and T2.B = ‘TRANSACTION FRAUD’ Then ‘Trans Fraud’
    End Department,

    Reply
  • Using MSSQL 2008 Adventureworks.

    The following syntax is wrong, but is something I want to do.
    i.e.
    If it is Friday today, select out all the data with Currency_Code IN (‘AED’, ‘AFA’, ‘ALL’)
    Else, select out Currency_Code IN (‘AED’, ‘AFA’)

    SELECT * FROM [AdventureWorks].[Sales].[Currency] a
    WHERE a.Currency_Code IN
    CASE DATEPART(dw, GetDate())
    WHEN 6 THEN ‘AED’, ‘AFA’, ‘ALL’ — Friday
    ELSE ‘AED’, ‘AFA’
    END

    Please with the SQL statement. Thanks

    Reply
    • Correction:
      Change a.Currency_Code to a.CurrencyCode

      Reply
    • Just to provide more info, this is what I want to achieve:

      DECLARE @var VARCHAR(1000)
      SELECT @var = CASE DATEPART(dw, GetDate())
      WHEN 6 THEN ”’AED”,”AFA”,”ALL”’
      ELSE ”’AED”,”AFA”’
      END
      PRINT @var

      — 1st SQL : does not return any result ???
      SELECT * FROM [AdventureWorks].[Sales].[Currency] a
      WHERE a.CurrencyCode IN (@var)

      SELECT @var = ‘SELECT * FROM [AdventureWorks].[Sales].[Currency] a
      WHERE a.CurrencyCode IN (‘+@var+’)’

      PRINT @var
      — 2nd SQL : This is the result I want if it is Friday.
      EXEC (@var)

      Since today is Friday, it will return as per the 2nd SQL:
      CurrencyCode Name ModifiedDate
      AED Emirati Dirham 1998-06-01 00:00:00.000
      AFA Afghani 1998-06-01 00:00:00.000
      ALL Lek 1998-06-01 00:00:00.000

      If today is not Friday, I’m expecting this result:
      CurrencyCode Name ModifiedDate
      AED Emirati Dirham 1998-06-01 00:00:00.000
      AFA Afghani 1998-06-01 00:00:00.000

      Thanks again

      Reply
  • Hi,

    I got three separate T-SQL scripts to run. Example

    search emp table for code

    select code,fname,lname from emp where fname =@lookfor

    if code = ‘A’
    select * from emp
    This is just an example i got more line sof tsql code to run if code =’a’.

    if code = ‘b’
    select @ from dept

    if code = ‘c’
    select * from add

    Reply
  • Hello everyone! This message could not be written better! Reading this post reminds me of my roommate before! He always kept talking about it. I will send this article to him. Pretty sure he will enjoy reading. Thank you for sharing!

    Reply
  • Hi All Experts.
    I came up with some problem that I am trying to solve, but no luck. Any help will be appreciated.

    Claim_id cycle First_date first_rank Second_date second rank 300 1 2011-02-25 0-13 2011-03-10 14-27 300 2 2010-09-15 14-27 2010-10-01 0-13 301 1 2011-04-14 14-27 2011-06-20 14-27 301 2 2010-08-04 14-27 2010-08-20 0-13 403 1 2011-02-09 14-27 2011-02-24 0-13 403 2 2010-08-13 28+ NULL NULL 404 1 2011-06-16 14-27 NULL NULL 404 2 2011-02-25 0-13 2011-03-08 14-27 404 3 2010-10-21 28+ 2010-11-16 14-27 405 1 2011-06-11 14-27 NULL NULL 405 2 2011-03-31 28+ NULL NULL 405 3 2010-08-10 28+ 2010-09-20 14-27 Following conditions are required:

    when first rank is 0-13 than records for cycle 1 (no matter what the second rank is) for that claim_id

    when first_rank is other than ‘0-13’ and second_rank is not null than records of cycle 1 is needed for that claimid

    when first_rank is other than ‘0-13’ and second_rank is null than next cycle record is needed for that claimid till second_rank is not null .

    Below is the required result:

    Claim_id cycle First_date first_rank Second_date second rank 300 1 2011-02-25 0-13 2011-03-10 14-27 301 1 2011-04-14 14-27 2011-06-20 14-27 403 1 2011-02-09 14-27 2011-02-24 0-13 404 2 2011-02-25 0-13 2011-03-08 14-27 405 3 2010-08-10 28+ 2010-09-20 14-27

    Reply
  • In table module is Integer datatype.

    select
    case @module
    when ‘None’ then 0
    when ‘Students’ then 1
    when ‘EMedExam’ then 2
    when ‘ARAP’ then 3
    when ‘OutReach’ then 4
    when ‘Referrals’ then 5
    when ‘EmpPortal’ then 6 end
    from SCTASKTABLE

    Im facing error conversion failed varchar to int

    please help me can any one send solution to my emailid

    [email removed]

    Thanks in Advance

    Reply
  • I want to retrieve the result based on the filter values if i pass o then i shud get all join result or if i pass filtered value then filtered result.But I am passing multiple values through parameter.
    Ex: customerId may have (0,1,2) like that.In that case how to write the where condition.If i dont select any value just join it

    ALTER PROCEDURE [dbo].[proc_get_akimbo_deails]
    –@pi_country_id int,
    –@pi_entity_id int,
    @pi_customer_id int
    –@pi_event_id int,
    –@pi_cpgid int
    –@from_date date,
    –@to_date date
    AS
    BEGIN

    select dh.akimbo_hid,cou.country_name,en.entity_name, c.customer_name,e.event_name,p.promo_type,dt.baseline,dt.co_packing,dt.pbi,dt.roi,dt.scc
    from akimbo_data_header dh
    inner join akimbo_customer_lookup c on c.customer_id=dh.customer_id
    inner join akimbo_entity_lookup en on en.entity_id=c.entity_id
    inner join akimbo_event_lookup e on e.event_id=dh.event_id
    inner join akimbo_country_lookup cou on cou.country_id=en.country_id
    inner join akimbo_data_transaction dt on dh.akimbo_hid=dt.akimbo_hid

    inner join akimbo_cpg_lookup cp on cp.cpg_id=dt.cpg_id
    inner join akimbo_promo_type_lookup p on p.promo_type_id=dt.promo_type_id

    where dh.customer_id =case when dh.customer_id in(@pi_customer_id) then dh.customer_id in (@pi_customer_id) else dh.customer_id end

    group by dh.akimbo_hid,cou.country_name,en.entity_name,
    c.customer_name,e.event_name,p.promo_type,dt.baseline,dt.co_packing,dt.pbi,dt.roi,dt.scc
    order by cou.country_name

    END

    Reply
  • On this example, I keep getting “Incorrect syntax near ‘>’ ”

    Example:
    DECLARE @TestVal INT
    SET @TestVal = 5
    SELECT
    CASE
    WHEN @TestVal <=3 THEN 'Top 3'
    ELSE 'Other'
    END

    Reply
  • I keep getting an error message when I use this example: “Incorrect syntax near ‘>’ ”

    Example:
    DECLARE @TestVal INT
    SET @TestVal = 5
    SELECT
    CASE
    WHEN @TestVal <=3 THEN 'Top 3'
    ELSE 'Other'
    END

    Reply
  • Hi experts. Newbie here.

    I’m trying to work out a select statement for sql svr 2008 that will eliminate credit for a 1/2 hour lunch break, if the person works less than 7 hours. The record for each worker has

    worker.minimumhours=7
    worker.lunchbreak = .5

    I would like to use a select statement with a case/when/end – something like

    DECLARE @StartDate AS DateTime
    SET @StartDate = CAST(’03/25/2012′ AS DATE)
    DECLARE @EndDate AS DateTime
    SET @EndDate = CAST(’03/31/2012′ AS DATE)

    SELECT
    w.Firstname
    ,w.Lastname
    ,wf.Login
    ,wf.logout
    ,ROUND(CAST(DATEDIFF(MI, wf.Login, wf.Logout) AS DECIMAL)/60,2) AS [Hours]
    ,w.LunchDeduction AS [Lunch Deduction]
    CASE DATEDIFF(hour, wf.Login, wf.Logout)
    WHEN = wf.MinimumHours THEN ROUND(CAST(DATEDIFF(MI, wf.Login, wf.Logout) AS DECIMAL)/60,- 0,2)
    END
    AS [Billedhours]
    FROM Workers AS w
    JOIN Workflow AS wf
    ON wf.LoggedInWorkerid = w.ID
    JOIN Roles AS r
    ON w.RoleID = r.RoleID
    WHERE r.Descript = ‘Hourly’
    AND wf.Login >= @StartDate AND wf.Logout <= @EndDate

    but get the error

    Msg 156, Level 15, State 1, Line 13
    Incorrect syntax near the keyword 'CASE'.

    Reply
  • Wooo. That’s not what I have. sorry.

    DECLARE @StartDate AS DateTime
    SET @StartDate = CAST(’03/25/2012′ AS DATE)
    DECLARE @EndDate AS DateTime
    SET @EndDate = CAST(’03/31/2012′ AS DATE)

    SELECT
    w.Firstname
    ,w.Lastname
    ,wf.Login
    ,wf.logout
    ,ROUND(CAST(DATEDIFF(MI, wf.Login, wf.Logout) AS DECIMAL)/60,2) AS [Hours]
    ,w.LunchDeduction AS [Lunch Deduction]
    CASE DATEDIFF(hour, wf.Login, wf.Logout)
    WHEN = wf.MinimumHours THEN ROUND(CAST(DATEDIFF(MI, wf.Login, wf.Logout) AS DECIMAL)/60,- 0,2)
    END
    AS [Billedhours]
    FROM Workers AS w
    JOIN Workflow AS wf
    ON wf.LoggedInWorkerid = w.ID
    JOIN Roles AS r
    ON w.RoleID = r.RoleID
    WHERE (r.Descript = ‘Hourly’
    OR r.Descript = ‘Salary’)
    AND wf.Login >= @StartDate AND wf.Logout <= @EndDate

    Reply
  • Hmm…. I have two when statements, that don’t seem to want to past in. Well it obviously doesn’t like the less then or great then signs, which should precede the wf.MinimumHours. I’m trying to check if their login and logout time difference in hours (to 2 decimal places) is less than wf.MinimumHours or greater than or equal to wf.MinimumHours.

    Reply
  • Hi,

    How can we include multiple case statement in single query

    Reply
  • Hi,
    Is there any possibility to set the expressions to a smaller name in the query?
    In the when clause of the searched case statement, if the column name is much larger to type every time, any solution for that?

    Reply
  • Select
    frmbrnchid,
    case when frmbrnch =”
    then
    (select brnchname from @var1.dbo.brnchmaster as fbm where fbm.brnchid = p.frmbrnchid and fbm.compid = p.frmcompid)
    else frmbrnch end as frmbrnchnm,
    from currtrans as p where p.entryno = 4

    i want to use @var1 in place of database name.. so please need a help for this..

    Reply
  • sorry for the previous post.. this is what i want

    my problem is that i need to use @var value as a database name… but i am not getting it.. this is what i am doing..

    declare @var1 varchar(700)

    set @var1 = (select compid from mastertables.dbo.compdet as fcd, CurrTrans as p where fcd.entryno = p.frmcompid)

    Select
    frmbrnchid,
    case when frmbrnch =”
    then
    (select brnchname from @var1.dbo.brnchmaster as fbm where fbm.brnchid = p.frmbrnchid and fbm.compid = p.frmcompid)
    else frmbrnch end as frmbrnchnm,
    from currtrans as p where p.entryno = 4

    i want to use @var1 in place of database name.. so please need a help for this..

    Reply
    • This is bad for security reasons. Here is an example

      declare @db varchar(20), @sql varchar(max)
      select @db=’test’,@sql=”
      select @sql=’select * from ‘+@db+’..dbo.brnchmaster’
      exec(@sql)

      Reply
  • Murali Krishna
    July 18, 2012 6:16 pm

    if i didnt pass any date value it is taking by default 1900-01-01 00:00:00.000.

    how to restrict that ? bcoz when i am displaying the value i dont display like that…..i wnt to display as empty blank. This i want to do in backend.

    Any idea?

    Reply
  • Declare @ToDt DateTime=’31-Jul-2012′
    Declare @nSchID Numeric(18,0)=0
    Declare @RptBrId Int=0
    Declare @Id As Numeric=1

    This Query Take more than 10 mins for fetching Data —
    SELECT CM.cSchCode, CM.cSchName, CM.cAccCode, CM.Cust1Nm ,CM.cSchCode+’ ‘+ CM.cSchName SchemFullName, TB.nOpBal, TB.nDebit, TB.nCredit, TB.nClBal, SC.cBranchName, CM.nCustID
    FROM Customers CM INNER JOIN TrailBalance TB ON CM.nAcID =TB.nAcId
    INNER JOIN SystemControl SC ON CM.nBranchId =Sc.nBranchId
    WHERE CM.EntType<=202 AND TB.Id=@Id
    AND CM.nBranchId BETWEEN CASE WHEN @RptBrId=0 THEN 0 ELSE @RptBrId END AND CASE WHEN @RptBrId=0 THEN 999 ELSE @RptBrId END
    AND ISNULL(CM.nSchID,0) BETWEEN CASE @nSchID WHEN 0 THEN 0 ELSE @nSchID END AND CASE @nSchID WHEN 0 THEN 999 ELSE @nSchID END
    AND ((CM.cAcStatus’C’) OR (CM.dStatusDate>=@ToDt AND CM.cAcStatus=’C’)) AND CM.dOpeningDate<=@ToDt

    But these query get only 1 sec.

    SELECT CM.cSchCode, CM.cSchName, CM.cAccCode, CM.Cust1Nm ,CM.cSchCode+' '+ CM.cSchName SchemFullName, TB.nOpBal, TB.nDebit, TB.nCredit, TB.nClBal, SC.cBranchName, CM.nCustID
    FROM Customers CM INNER JOIN TrailBalance TB ON CM.nAcID =TB.nAcId
    INNER JOIN SystemControl SC ON CM.nBranchId =Sc.nBranchId
    WHERE CM.EntType<=202 AND TB.Id=@Id
    –AND CM.nBranchId BETWEEN CASE WHEN @RptBrId=0 THEN 0 ELSE @RptBrId END AND CASE WHEN @RptBrId=0 THEN 999 ELSE @RptBrId END
    AND ISNULL(CM.nSchID,0) BETWEEN CASE @nSchID WHEN 0 THEN 0 ELSE @nSchID END AND CASE @nSchID WHEN 0 THEN 999 ELSE @nSchID END
    AND ((CM.cAcStatus’C’) OR (CM.dStatusDate>=@ToDt AND CM.cAcStatus=’C’)) AND CM.dOpeningDate<=@ToDt

    or

    SELECT CM.cSchCode, CM.cSchName, CM.cAccCode, CM.Cust1Nm ,CM.cSchCode+' '+ CM.cSchName SchemFullName, TB.nOpBal, TB.nDebit, TB.nCredit, TB.nClBal, SC.cBranchName, CM.nCustID
    FROM Customers CM INNER JOIN TrailBalance TB ON CM.nAcID =TB.nAcId
    INNER JOIN SystemControl SC ON CM.nBranchId =Sc.nBranchId
    WHERE CM.EntType<=202 AND TB.Id=@Id
    AND CM.nBranchId BETWEEN CASE WHEN @RptBrId=0 THEN 0 ELSE @RptBrId END AND CASE WHEN @RptBrId=0 THEN 999 ELSE @RptBrId END
    –AND ISNULL(CM.nSchID,0) BETWEEN CASE @nSchID WHEN 0 THEN 0 ELSE @nSchID END AND CASE @nSchID WHEN 0 THEN 999 ELSE @nSchID END
    AND ((CM.cAcStatus’C’) OR (CM.dStatusDate>=@ToDt AND CM.cAcStatus=’C’)) AND CM.dOpeningDate<=@ToDt

    i can't understand how this take.

    please help me

    Thanks in advance

    Reply
  • I am not sure if I missed a question like this. I would like to test one field, and then perform CASE/comparison on another field.
    I hope I can explain my thought clearly. I would like to test Delivery and based on its value, test Charge_Type.
    One Order can have multiple charges associated with it.

    In plain English:
    If Delivery = ‘PrePay’
    Then IF
    test Charge_Type. If Charge_Type like “FR%”, then Charge_Amt as PrePay
    test Charge_Type. If Charge_Type = “INS”, then Charge_Amt as Other
    test Charge_Type. If Charge_Type = “NRG”, then Charge_Amt as Energy
    ELSE Prepay = 0
    If Delivery = ‘Collect’
    Then IF
    test Charge_Type. If Charge_Type like “FR%”, then Charge_Amt as Collect
    test Charge_Type. If Charge_Type = “INS”, then Charge_Amt as Other
    test Charge_Type. If Charge_Type = “NRG”, then Charge_Amt as Energy
    ELSE Collect = 0

    What I am trying to do is look at the freight types (I have 6) and then compare the Charge_Types (I have 10). Really, the INS/NRG – these will always go to one column. I guess it is the FR% and Delivery fields that would put values in another column.
    I may have over complicated this. Too many if.then, but that was to try and explain the logic in plain English

    Reply
    • I found that I can use a CASE within a CASE, and maybe this might help. The overall goal was to build a result set where I can have four derived columns AS depending on the value of Charge_Amt or Delivery fields. I will be aggregating the values too. This is a subselect of another one, so I would hope the subselect result would be be Order_num, PrePay, Collect, Energy, Other

      Reply
  • Thank you for the explanation and examples. Saved my day. I used the select case sql in one my of programs and it worked wonders!

    Reply

Leave a Reply

Menu