October 12, 2011 at 4:10 am
Hi guys,
i want to get the result set based on a @startdate if the date is given i need to fetch data as per the given range if its passed as null i need to select all the records from table.
here is my query.
i can do it through a if statement, on that case i have to repeat the same query which i dont want, if a will be able to achieve this through case, its will reduce the code also
declare @StartDate datetime,@EndDate datetime
set @StartDate='2011-04-01 00:00:00.000'
set @StartDate='2011-04-30 00:00:00.000'
select distinct
convert(bigint,ICUSTNO)
,case when convert(bigint,Affno)= convert(bigint,ICUSTNO) then ''
Else isnull( convert(nvarchar(max),convert(bigint,Affno)),'') End as [LoadId:Organizations:Parent]
,CUSTNAME as [CompanyName]
,'' as [ContactNumbers:BusinessPhone]
,'' as [ContactNumbers:Fax]
,'' as [OnlineAddresses:PrimaryEmail]
,ADDRSS1 as [Address1@Addresses:Business]
,ADDRSS2 as [Address2@Addresses:Business]
,CITY as [City@Addresses:Business]
,STATE as [State@Addresses:Business]
,ZIP as [Zip@Addresses:Busines]
,'USA' as [Country@Addresses:Business]
From AvalonSourceTable
where CASE WHEN @StartDate IS NOT NULL THEN convert(datetime,install,101) between @StartDate AND @EndDate --'2011-04-01 00:00:00.000'
--and convert(datetime,install,101) <= @EndDate--'2011-04-30 00:00:00.000'
else 1=1
end
above query is not correct as its giving syntax error near dis line
where CASE WHEN @StartDate IS NOT NULL THEN convert(datetime,install,101) between @StartDate AND
looking for some gud tips.
i m not posting the table structure and sample data coz its only a syntax problem, sry for that :hehe:
October 12, 2011 at 4:24 am
You definitely can't use case statements like that (nor do you need to).
This is a classic example of a Catch-All query.
The logical way to convert what you're trying to do into valid syntax would be something like this:
where (convert(datetime,install,101) between @StartDate AND @EndDate OR @StartDate is NULL)
Why is install not already a datetime if it holds datetime values?
This kind of catch-all query has performance downsides (although looking at your code, I'm guessing this isn't the top of your priority list).
You should look at Gail's article below for better ways to do this:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
October 12, 2011 at 6:07 am
Thanks howard,
i got the query, in my DB there are few desing issues are there but can't help as my company is using this structure from long time, although this query i was using for migration purpose for small amount of data.
i gone through the "catch all queries" article of gail, its really a nice one(as it is all of his articles are great) i am a follower of gail,jeff and also jeo celko.:-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply