Blog : Journey to SQL Authority with Pinal Dave

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 expressionWHEN expression1 THEN expression1
[[WHEN expression2 THEN expression2] [...]]
[ELSE expressionN]END
Example:
DECLARE @TestVal INT
SET @TestVal = 3SELECTCASE @TestValWHEN 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:
CASEWHEN Boolean_expression1 THEN expression1
[[WHEN Boolean_expression2 THEN expression2] [...]]
[ELSE expressionN]END
Example:
DECLARE @TestVal INT
SET @TestVal = 5SELECTCASEWHEN @TestVal <=3 THEN 'Top 3'ELSE 'Other'END

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

Reply

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 ?

You need to use this logic

select *
FROM from employees
WHERE
(a=1 and name = 'Jim') or
(a=2 and EmployeeID = 90) or
(a=4 and EmployeeStatus = 'Fired')

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.

Use this

select * from table1
where ((code in ('a','b','c') and @type=1) or @type1)

My above reply should be

Use this

select * from table1
where ((code in ('a','b','c') and @type=1) or @type11)

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.

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.

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

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.

No. You need to use IF clause for this

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

What did you mean by not working?
Use NULL in place of �

I am trying to use a 2 case statements inside one table. both statements work but they dont work together, any ideas??

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

Pls give me sql 2008 Note

hi chalana send me ur number i will give u note

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

@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

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.

Reply

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

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′

you need this at the end of your code

end
)

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:

Reply

@krish

Why not use REPLACE() for the entire string? No WHILE loop or anything, just a simple REPLACE(@name, ‘;’, �)

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

@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

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

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

Reply

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

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

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

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

1. • Use

     P1.FOOTER IS NULL

     instead of

     P1.FOOTER = NULL

     Reply

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

   Reply

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

   Reply

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

   Reply

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

   Reply

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

   Reply

   • @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;

     Reply

7. Thank You Brian Tkatch !

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

   Reply

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

     Reply

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

   Reply

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

   Reply

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

    Reply

    • 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

      Reply

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

    Reply

    • Hi,

      That’s almost correct. Try this:

      CASE
      WHEN @variable IN (1,2,3) THEN ..
      WHEN @variable IN (4,5,6) THEN ..
      END

      Reply

      • Thanks Polelo

        Reply

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

    Reply

    • @Andrea

      How’s this?

      UPDATE
      prd
      SET
      WHLoc = CASE comcod
      WHEN 1 THEN ‘SPL’
      WHEN 2 THEN ‘SCM’
      END
      WHERE
      whloc IS NULL;

      Reply

      • Hi Brian,

        Thanks for your help!

        Your advice works great for my problem!

        rgds/Andrea

        Reply

13. Hello,I am a 13 year old boy,Visual Basic is in my sylabbus,Please contact me on chitu_smarty@yahoo.co.in

    Reply

14. Hi is it posible to write something like

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

    thanks in advance

    Reply

    • @Thato

      Yes. But male male and female should also be in quotes.

      Reply

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

    Reply

    • You need to use IF clause instead of CASE Expression

      Reply

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

    Reply

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

    Reply

    • Update employee set salary =salary+
      CASE
      WHEN @salary =1000
      100
      Else
      10
      End;

      Reply

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

    Reply

    • 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

      Reply

      • ThanQ Madhivanan

        Reply

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

    Reply

    • Post some sample data with expected result

      Reply

20. Try This one it may work

    select min(YourColumnName) as MinimuValue from tablename where YourColumnName > 0

    Reply

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

    Reply

    • @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;

      Reply

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

    Reply

    • This wont work if the subquery returns more than one row

      Reply

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

    Reply

    • You need to make use of a trasaction to avoid the error. Aslo just before adding data to table query for max(col)+1

      Reply

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

    Reply

25. Nice, but explanation is too short and not describe complex queries.

    need to explain some complex queries using case.

    Reply

    • Refer this post to know more examples about the CASE expression
      http://beyondrelational.com/blogs/madhivanan/archive/2010/08/24/case-expression.aspx

      Reply

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

    Reply

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

    Reply

    • Try this

      Select *
      From Table as a
      Where
      (Code Not in (1,2,3,4) and Date > ’10-02-2010′ )
      OR
      (Date <- ’10-02-2010′ and Code not in(5,6,7,8))

      Reply

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

    Reply

    • Post the code you used

      Reply

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

    Reply

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

    Reply

    • You need to post what the problem with your query

      Reply

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

    • You need to post some sample data with expected result

      Reply

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

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

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

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

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

    • Hi Mani,

      Please check this solution http://blog.sqlauthority.com/2007/07/08/sql-server-fix-error-msg-244-level-16-state-1-fix-error-msg-245-level-16-state-1/

      Reply

    • What is the datatype of @module?

      Reply

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

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

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

    • Is this the full code are you using? It does not have any character like >

      Reply

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

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

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

43. Hi,

    How can we include multiple case statement in single query

    Reply

    • just simply separate the case statements with a comma(,)

      Reply

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

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

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

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