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 (http://blog.SQLAuthority.com)

About these ads

141 thoughts on “SQL SERVER – CASE Statement/Expression Examples and Explanation

  1. Hi,

    Nice article , however, can you guide how we can manage the case statement when there are more then 600 options to select from.

  2. 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]);

  3. 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 ?

  4. 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.

  5. 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…

  6. 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.

  7. 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

  8. 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.

  9. 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

  10. 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?

  11. 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

  12. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  13. 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

    • Use derived table

      select * from
      (
      Select id, name,
      CASE
      WHEN AGE+@SomeVariable<17 THEN ‘Young’
      ELSE AGE END as AGE
      from users
      ) as t
      where AGE='some condition'

      Also it is not good practice to convert numbers to text

  14. @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.

  15. 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.

  16. 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

  17. 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’

  18. 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:

  19. 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

  20. @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

  21. Hi Experts,

    Is anyway i can write following query in Case statement? please guide me

    (
    ( ( @Variable(‘EntityType’) = ‘DEPARTMENT’ ) AND ( @Variable(‘EntityCode’) = ‘–ALL–‘ ) )
    OR ( (@Variable(‘EntityType’) = ‘DEPARTMENT’) AND(@Variable(‘EntityCode’) != ‘–ALL–‘ ) AND (LTRIM(RTRIM(Alias_dbo_SAF_ORG_STRUCTURE.DEPT_CD)) IN (select * from ufn_multi_value_to_tbl(@Variable(‘EntityCode’)))))

    OR ((@Variable(‘EntityType’) = ‘RAILROAD’) AND (@Variable(‘EntityCode’) = ‘–ALL–‘))
    OR ((@Variable(‘EntityType’) = ‘RAILROAD’) AND (@Variable(‘EntityCode’) !=’–ALL–‘) AND (LTRIM(RTRIM(Alias_dbo_SAF_ORG_STRUCTURE.RAILROAD_CD)) IN (select * from ufn_multi_value_to_tbl(@Variable(‘EntityCode’)))))
    OR ((@Variable(‘EntityType’) = ‘DIVISION’) AND (@Variable(‘EntityCode’) = ‘–ALL–‘))
    OR ((@Variable(‘EntityType’) = ‘DIVISION’) AND (@Variable(‘EntityCode’) !=’–ALL–‘) AND (LTRIM(RTRIM(Alias_dbo_SAF_ORG_STRUCTURE.DIVISION_CD)) IN (select * from ufn_multi_value_to_tbl(@Variable(‘EntityCode’)))))
    OR ((@Variable(‘EntityType’) = ‘FUNCTION’) AND (@Variable(‘EntityCode’) = ‘–ALL–‘))
    OR ((@Variable(‘EntityType’) = ‘FUNCTION’) AND (@Variable(‘EntityCode’) !=’–ALL–‘) AND (LTRIM(RTRIM(Alias_dbo_SAF_ORG_STRUCTURE.FUNCTION_CD)) IN (select * from ufn_multi_value_to_tbl(@Variable(‘EntityCode’)))))
    OR ((@Variable(‘EntityType’) = ‘SUBFUNCTION’) AND (@Variable(‘EntityCode’) = ‘–ALL–‘))
    OR ((@Variable(‘EntityType’) = ‘SUBFUNCTION’) AND (@Variable(‘EntityCode’) !=’–ALL–‘) AND (LTRIM(RTRIM(Alias_dbo_SAF_ORG_STRUCTURE.SUBFUNCTION_CD)) IN (select * from ufn_multi_value_to_tbl(@Variable(‘EntityCode’)))))
    OR ((@Variable(‘EntityType’) = ‘RESCEN’) AND (@Variable(‘EntityCode’) = ‘–ALL–‘))
    OR ((@Variable(‘EntityType’) = ‘RESCEN’) AND (@Variable(‘EntityCode’) !=’–ALL–‘) AND (LTRIM(RTRIM(Alias_dbo_SAF_ORG_STRUCTURE.RESCEN_CD)) IN (select * from ufn_multi_value_to_tbl(@Variable(‘EntityCode’)))))
    )

    Thank you
    PV

    • @PV

      How’s this?

      WHERE
      @Variable(‘EntityCode’) = ‘–ALL–’
      OR LTRIM
      (
      RTRIM
      (
      CASE (@Variable(‘EntityType’)
      WHEN ‘DEPARTMENT’ THEN Alias_dbo_SAF_ORG_STRUCTURE.DEPT_CD
      WHEN ‘RAILROAD’ THEN Alias_dbo_SAF_ORG_STRUCTURE.RAILROAD_CD
      WHEN ‘DIVISION’ THEN Alias_dbo_SAF_ORG_STRUCTURE.DIVISION_CD
      WHEN ‘FUNCTION’ THEN Alias_dbo_SAF_ORG_STRUCTURE.FUNCTION_CD
      WHEN ‘SUBFUNCTION’ THEN Alias_dbo_SAF_ORG_STRUCTURE.SUBFUNCTION_CD
      WHEN ‘RESCEN’ THEN Alias_dbo_SAF_ORG_STRUCTURE.RESCEN_CD
      END
      )
      ) IN (SELECT * FROM ufn_multi_value_to_tbl(@Variable(‘EntityCode’)

  22. Hello Experts, i want to set a variable and use the value later for processing, i am getting an error when using the below..

    SELECT
    CASE @TestVal
    WHEN 1 THEN set @var= ‘First’
    WHEN 2 THEN Set @var= ‘Second’
    WHEN 3 THEN set @var= ‘Third’
    ELSE ‘Other’
    END
    ===================
    insert into table (@var, etc….)

  23. Hi Anil,

    Correct syntax to assign a value to variable with CASE statement is:

    SELECT @var=
    CASE @TestVal
    WHEN 1 THEN set ‘First’
    WHEN 2 THEN Set ‘Second’
    WHEN 3 THEN set ‘Third’
    ELSE ‘Other’
    END

    Thanks,

    Tejas
    SQLYoga.com

  24. Hello Anil,

    Use the SELECT CASE as below:

    SELECT @var =
    CASE @TestVal
    WHEN 1 THEN ‘First’
    WHEN 2 THEN ‘Second’
    WHEN 3 THEN ‘Third’
    ELSE ‘Other’
    END

    Regards,
    Pinal Dave

  25. Dear All

    Kindly correct the given below part of query.

    SELECT distinct(P1.DOCNUM),P6.DOCENTRY,

    Case when P1.FOOTER = NULL THEN

    P1.U_POTRD , P1.U_EXARW , P1.U_ConNum as ContainerNo, P1.U_Vessel_Flight_no as VesselNo, ,P1.U_CDIBOL as BLNumber, cast(P1.FOOTER as varchar(max))as FOOTER ,
    ELSE
    P5.U_POTRD , P5.U_EXARW , P5.U_ConNum as ContainerNo, P5.U_Vessel_Flight_no as VesselNo, ,P5.U_CDIBOL as BLNumber, cast(P5.FOOTER as varchar(max))as FOOTER,
    Endcase
    ——-
    Problem with case part.

    With Regards,

    Nirbhay

  26. I’m a novice (if that). I’m trying to use MS Query to send data to Excel and I’m only trying to pull ACTNCNT when ACTNCNT is >0, ELSE ESTNCNT.
    In Excel it would be =IF (ACTNCNT > 0, ACTNCNT, ESTNCNT). In Crystal, I would be able to create a formula field I could show the output for – NCNT; IIF (ACTNCNT > 0, ACTNCNT, ESTNCNT).

    Is there anyway to pass “NCNT” to Excel? Or do I need to pull both and run a macro?

    I’m also having issue using a parameter. When I’m in MS Query I can use OCCURRED>=[From Date]AND=#1/28/2010 6:00:00 AM# And <=#1/29/2010 6:00:00 AM#"; everything works.

    Any thoughts?

  27. @Jason

    Your question is not clear.

    Are you trying to export data from MS SQL Server to excel sheet. What tool are you using, like SSIS/DTS/openquery ?

    Writing a case statement in DTS/SSIS data flow task is very easy.

    Please explain clearly what is your requirement.

    ~ IM

  28. Im trying to use 2 CASE statements together in a SELECT query without much luck.
    The second CASE uses the output from the first CASE as it’s expression (well it’s supposed to).

    SELECT

    accountNum

    , balanceCalculation = case WHEN Balance > 5000 THEN Balance *2
    ELSE Balance /2
    END

    , balanceDescription = CASE WHEN balanceCalculation > 6000 THEN ‘Large Balance’
    ELSE ‘small balance’
    END

    FROM tblFinances

  29. @Ross

    Check if this helps.

    SELECT accountNum
    , balanceCalculation = CASE
    WHEN Balance > 5000 THEN Balance *2
    ELSE Balance /2
    END
    , balanceDescription = CASE WHEN (
    CASE
    WHEN Balance > 5000 THEN Balance *2
    ELSE Balance /2
    END
    ) > 6000 THEN ‘Large Balance’
    ELSE ’small balance’
    END
    FROM tblFinances

    ~ IM.

  30. How do I use case when Im want to compare not only the column under question but also another column that has a said value.

    For example table a has col1, col2, col3. Now when I want to see what is stored in Col1 in conjunction with Col2, how do I do that.

    For example

    Col1 Col2 Col3
    1 5 44
    2 6 12
    3 ” 1
    3 A 3

    Now here, I want to update the value of Col3 under the following conditions –
    1. When Col3 value = 1 and
    2. When Col2 value = Null then set the value of Col3 to Null.

    How would I do this ?

    Thanks,
    Tim K Liu

    • @Tim k

      SET Col3 = CASE WHEN Col3 = 1 AND Col2 IS NULL THEN NULL ELSE Col3 END

      Or, the inverse:

      SET Col3 = CASE WHEN Col3 1 AND Col2 IS NOT NULL THEN Col3 END

      Although, this could usually be done in ther WHERE clause:

      UPDATE mytab SET Col3 = NULL WHERE Col3 = 1 AND Col2 IS NULL;

  31. Thank You Brian Tkatch !

    I figured the second one by myself but I followed your firststep as it is a bit more readable & understandable.

    • @Tim

      Yeah, that’s why i put it there. :) The second one is cleaner, the first one is easier.

      Note that the third option is probably best, as the CASE will UPDATE every single record, either to NULL or to itself. If it is in the WHERE clause, it will only UPDATE the records that need changing.

      This is beneficial because:

      1) In the first case an AFTER UPDATE TRIGGER will fire even if the record is SET to itself.

      2) For a large TABLE, setting a small subset is usually faster. The CASE would UPDATE every record, so it might take longer.

      3) If another process has the row locked, and this UPDATE needs to change the value, that is fine. But if it doesn’t need to change it, and it just sets it to itself, the CASE can cause a wait until the record is no longer locked (or timesout).

  32. hi am trying to do a consultation with case and brand me a mistake, could you help please???

    excuse the grammar, do not speak English

  33. hi am trying to do a consultation with case and brand me a mistake, could you help please???

    excuse the grammar, do not speak English

    USE mantto1;
    GO
    DECLARE @DiaReporte datetime, @fir datetime, @diasR as int;
    SET ANSI_WARNINGs OFF
    SELECT @DiaReporte = ‘9/02/2010′, @fir=min(fa_real), @diasR=datediff (dd,actividades.fecha_i_p,@fir)
    FROM avances , actividades
    where actividades.clave=avances.clave
    group by actividades.fecha_i_p
    select case when @diasR>0 then
    SELECT @DiaReporte as fecha_reporte, @fir as fecha_inicial_r, @diasR as dias_retraso

    GO

  34. sorry, this is the qerry, i have a mistake but i don’t know what is it :(

    USE mantto1;
    GO
    DECLARE @DiaReporte datetime, @fir datetime, @diasR as int;
    SET ANSI_WARNINGs OFF
    SELECT @DiaReporte = ‘9/02/2010′, @fir=min(fa_real), @diasR=datediff (dd,actividades.fecha_i_p,@fir)
    FROM avances , actividades
    where actividades.clave=avances.clave
    group by actividades.fecha_i_p
    select case when @diasR>0 then
    SELECT @DiaReporte as fecha_reporte, @fir as fecha_inicial_r, @diasR as dias_retraso

    GO

    • select case when @diasR>0 then
      SELECT @DiaReporte as fecha_reporte, @fir as fecha_inicial_r, @diasR as dias_retraso

      Probably should be:

      IF @diasR > 0 SELECT @DiaReporte AS fecha_reporte, @fir AS fecha_inicial_r, @diasR AS dias_retraso

  35. 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

  36. 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!

  37. Hi is it posible to write something like

    Case
    when name in (‘john’, ‘ben’, ‘david’) then male else female end

    thanks in advance

  38. 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?

  39. 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’.

  40. 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

  41. 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

  42. 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

  43. 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;

  44. 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

  45. 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…

  46. 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

  47. 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

  48. 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?

  49. 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.

  50. 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!!

  51. 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

  52. 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,

  53. 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

    • 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

  54. 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

  55. 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!

  56. 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

  57. 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

  58. 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

  59. 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

  60. 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

  61. 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'.

  62. 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

  63. 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.

  64. 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?

  65. 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..

  66. 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..

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

  67. 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?

  68. 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

  69. 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

    • 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

  70. 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

  71. 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.

    • 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

  72. 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

  73. 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..

  74. Pingback: SQL SERVER – Weekly Series – Memory Lane – #025 | SQL Server Journey with SQL Authority

  75. 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

  76. 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.

  77. 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 ‘>’.

  78. 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?

  79. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s