using case in where clause having other conditions

Question

Post reply

using case in where clause having other conditions

ssurekha2000

SSCommitted

Points: 1860
More actions
June 12, 2013 at 12:19 am

#295160

i have a stored procedure where paramaters are passed from the application
i need to apply case in where clause of the existing stored procedure
a query is generated as
@id as int,
@dt1 AS datetime,
@dt2 as datetime,
@con as int,
@YMonth as varchar(10)
declare @dt3 as varchar
declare @dt4 as varchar
---- @dt3,@dt4 are generated in the procedure
select * from table1 where id=@id
and
case when @con=0 then
YEARMONTH >=Replace(@YearMonth,'-','')
else
YEARMONTH <>Replace(@YearMonth,'-','')
end as YMonth
and
case when @con=0 then
CONVERT(datetime,RDATE, 103) >= convert(datetime,@dt3,103) AND CONVERT(datetime,MD.RDATE, 103)<= convert(datetime,@dt4,103)
else
CONVERT(datetime,RDATE, 103) >= convert(datetime,@dt1,103) AND CONVERT(datetime,MD.RDATE, 103)<= convert(datetime,@dt2,103)
end as RDATE
and imtid<> 0
but i am not getting it

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

-- use APPLY to evaluate your filters

SELECT *

FROM table1

CROSS APPLY (

SELECT

Filter1 = CASE

WHEN @con = 0 AND YEARMONTH >= REPLACE(@YearMonth,'-','') THEN 1

WHEN YEARMONTH <> REPLACE(@YearMonth,'-','') THEN 2

ELSE NULL END,

Filter2 = CASE

WHEN @con = 0

AND CONVERT(datetime, RDATE, 103) >= convert(datetime, @dt3, 103)

AND CONVERT(datetime, MD.RDATE, 103) <= convert(datetime, @dt4, 103) THEN 1

WHEN CONVERT(datetime, RDATE, 103) >= convert(datetime, @dt1, 103)

AND CONVERT(datetime, MD.RDATE, 103) <= convert(datetime, @dt2, 103) THEN 2

ELSE NULL END

) x

WHERE id = @id

AND imtid <> 0

-- and to check they work as you expect them to

SELECT *

FROM table1

CROSS APPLY (

SELECT

Filter1 = CASE

WHEN @con = 0 AND YEARMONTH >= REPLACE(@YearMonth,'-','') THEN 1

WHEN YEARMONTH <> REPLACE(@YearMonth,'-','') THEN 2

ELSE NULL END,

Filter2 = CASE

WHEN @con = 0

AND CONVERT(datetime, RDATE, 103) >= convert(datetime, @dt3, 103)

AND CONVERT(datetime, MD.RDATE, 103) <= convert(datetime, @dt4, 103) THEN 1

WHEN CONVERT(datetime, RDATE, 103) >= convert(datetime, @dt1, 103)

AND CONVERT(datetime, MD.RDATE, 103) <= convert(datetime, @dt2, 103) THEN 2

ELSE NULL END

) x

WHERE id = @id

AND imtid <> 0

AND x.Filter1 = 1

AND x.Filter2 = 2

-- when you are absolutely sure everything is working as it should,

-- you can switch the filters back to the WHERE clause. You don't have to.

-- The execution plan is likely to be identical and retaining the APPLY block

-- aids readability.

SELECT *

FROM table1

WHERE id = @id

AND imtid <> 0

AND (CASE

WHEN @con = 0 AND YEARMONTH >= REPLACE(@YearMonth,'-','') THEN 1

WHEN YEARMONTH <> REPLACE(@YearMonth,'-','') THEN 2

ELSE NULL END) = 1

AND (CASE

WHEN @con = 0

AND CONVERT(datetime, RDATE, 103) >= convert(datetime, @dt3, 103)

AND CONVERT(datetime, MD.RDATE, 103) <= convert(datetime, @dt4, 103) THEN 1

WHEN CONVERT(datetime, RDATE, 103) >= convert(datetime, @dt1, 103)

AND CONVERT(datetime, MD.RDATE, 103) <= convert(datetime, @dt2, 103) THEN 2

ELSE NULL END) = 2

-- WARNING: using functions on your table columns will almost always prevent

-- SQL Server from using an index on those columns, resulting in suboptimal performance.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ssurekha2000 SSCommitted Points: 1860 More actions · Answer 2

not fully understood

using the above i am not getting any records

but in query analyser i get 100 records with date condition & id condition

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

ssurekha2000 (6/12/2013)
not fully understood
using the above i am not getting any records
but in query analyser i get 100 records with date condition & id condition

Please provide more detail. Your query has numerous syntax errors and will not run, it cannot return 100 rows. If you do have a query which generates 100 rows, then please post it.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ssurekha2000 SSCommitted Points: 1860 More actions · Answer 4

the whole sp cannot be posted as there are many joins

to get the result & check i prepared 3 different SPs with conditions as stated above

SP1 contains @dt1 & @dt2 date passed

SP2 contains @dt3 & @dt4 date passed

SP3 contains @dt1 & @dt2 & @dt3 & @dt4 date passed with where clause as stated by you

when SP1 & SP2 are executed i get the proper results

but if i executue the SP with case in where clause i get no records

individually in SP1 i get 13 records

SP2 i get 6 records

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

ssurekha2000 (6/12/2013)
the whole sp cannot be posted as there are many joins
to get the result & check i prepared 3 different SPs with conditions as stated above
SP1 contains @dt1 & @dt2 date passed
SP2 contains @dt3 & @dt4 date passed
SP3 contains @dt1 & @dt2 & @dt3 & @dt4 date passed with where clause as stated by you
when SP1 & SP2 are executed i get the proper results
but if i executue the SP with case in where clause i get no records
individually in SP1 i get 13 records
SP2 i get 6 records

Please post the whole query for SP3. Without it, we're guessing.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ssurekha2000 SSCommitted Points: 1860 More actions · Answer 6

i got it using

where id=@id

and (

(@con=0 AND YEARMONTH >=Replace(@YearMonth,'-',''))

OR (@con<>0 AND YEARMONTH <>Replace(@YearMonth,'-',''))

)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

ssurekha2000 (6/13/2013)
i got it using
where id=@id
and (
(@con=0 AND YEARMONTH >=Replace(@YearMonth,'-',''))
OR (@con<>0 AND YEARMONTH <>Replace(@YearMonth,'-',''))
)

Hope that it's a small row set and excellent performance is not a requirement...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ssurekha2000 SSCommitted Points: 1860 More actions · Answer 8

if it can be done by another way which is faster let me know

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Need to see the entire query.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass