June 12, 2013 at 12:19 am
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
June 12, 2013 at 2:00 am
-- 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.
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
June 12, 2013 at 3:10 am
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
June 12, 2013 at 4:23 am
ssurekha2000 (6/12/2013)
not fully understoodusing 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.
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
June 12, 2013 at 11:57 pm
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
June 13, 2013 at 1:00 am
ssurekha2000 (6/12/2013)
the whole sp cannot be posted as there are many joinsto 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.
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
June 13, 2013 at 2:39 am
i got it using
where id=@id
and (
(@con=0 AND YEARMONTH >=Replace(@YearMonth,'-',''))
OR (@con<>0 AND YEARMONTH <>Replace(@YearMonth,'-',''))
)
June 13, 2013 at 3:05 am
ssurekha2000 (6/13/2013)
i got it usingwhere 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
June 13, 2013 at 3:12 am
if it can be done by another way which is faster let me know
June 13, 2013 at 3:24 am
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply