Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

using case in where clause having other conditions Expand / Collapse
Author
Message
Posted Wednesday, June 12, 2013 12:19 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:04 AM
Points: 79, 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
Post #1462469
Posted Wednesday, June 12, 2013 2:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 6,750, Visits: 13,896
-- 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
Post #1462491
Posted Wednesday, June 12, 2013 3:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:04 AM
Points: 79, 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
Post #1462513
Posted Wednesday, June 12, 2013 4:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 6,750, Visits: 13,896
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
Post #1462542
Posted Wednesday, June 12, 2013 11:57 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:04 AM
Points: 79, 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
Post #1462906
Posted Thursday, June 13, 2013 1:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 6,750, Visits: 13,896
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
Post #1462916
Posted Thursday, June 13, 2013 2:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:04 AM
Points: 79, Visits: 207
i got it using

where id=@id
and (
(@con=0 AND YEARMONTH >=Replace(@YearMonth,'-',''))
OR (@con<>0 AND YEARMONTH <>Replace(@YearMonth,'-',''))
)
Post #1462938
Posted Thursday, June 13, 2013 3:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
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 2008, MVP
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

Post #1462946
Posted Thursday, June 13, 2013 3:12 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:04 AM
Points: 79, Visits: 207
if it can be done by another way which is faster let me know
Post #1462948
Posted Thursday, June 13, 2013 3:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
Need to see the entire query.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1462949
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse