SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


using case in where clause having other conditions


using case in where clause having other conditions

Author
Message
ssurekha2000
ssurekha2000
SSC-Addicted
SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)

Group: General Forum Members
Points: 403 Visits: 207
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
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40760 Visits: 20000
-- 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
Exploring Recursive CTEs by Example Dwain Camps
ssurekha2000
ssurekha2000
SSC-Addicted
SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)

Group: General Forum Members
Points: 403 Visits: 207
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
ChrisM@Work
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40760 Visits: 20000
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
Exploring Recursive CTEs by Example Dwain Camps
ssurekha2000
ssurekha2000
SSC-Addicted
SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)

Group: General Forum Members
Points: 403 Visits: 207
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
ChrisM@Work
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40760 Visits: 20000
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
Exploring Recursive CTEs by Example Dwain Camps
ssurekha2000
ssurekha2000
SSC-Addicted
SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)

Group: General Forum Members
Points: 403 Visits: 207
i got it using

where id=@id
and (
(@con=0 AND YEARMONTH >=Replace(@YearMonth,'-',''))
OR (@con<>0 AND YEARMONTH <>Replace(@YearMonth,'-',''))
)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220413 Visits: 46279
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
ssurekha2000
SSC-Addicted
SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)

Group: General Forum Members
Points: 403 Visits: 207
if it can be done by another way which is faster let me know
GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220413 Visits: 46279
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search