March 6, 2015 at 5:26 am
you can replace this part of the query
"Last_Status_DT >= '2013-10-25'
AND Last_Status_DT < '2013-10-29';"
For this one
"Last_Status_DT between '2013-10-25' and '2013-10-29';"
And the code are little more simple.
March 6, 2015 at 5:35 am
omarlopez7 (3/6/2015)
you can replace this part of the query"Last_Status_DT >= '2013-10-25'
AND Last_Status_DT < '2013-10-29';"
For this one
"Last_Status_DT between '2013-10-25' and '2013-10-29';"
And the code are little more simple.
No you can't. Your suggestion includes '2013-10-29', whereas < '2013-10-29' does not.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 6, 2015 at 5:58 am
This will do the same you have in the where section, go from day 25 to 29. Give it a try,i used before and works great.
March 6, 2015 at 6:08 am
omarlopez7 (3/6/2015)
This will do the same you have in the where section, go from day 25 to 29. Give it a try,i used before and works great.
Here is me 'giving it a try'. Your statement is incorrect and that is proved below.
declare @StartDate date = '2013-10-25'
declare @EndDate date = '2013-10-29'
declare @TestDate date = '2013-10-29'
if @TestDate between @StartDate and @EndDate
select 'Between test passed'
if @TestDate >= @StartDate
and @TestDate < @EndDate
select 'Range test passed'
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 6, 2015 at 6:13 am
In that case not work, i says if you us on the where section like this.
Select * from table
where date between '12-12-12' and '12-31-12'
March 6, 2015 at 6:26 am
omarlopez7 (3/6/2015)
In that case not work, i says if you us on the where section like this.Select * from table
where date between '12-12-12' and '12-31-12'
Between is inclusive of the start and end dates, that was the point. The original code does not include the end date. You need to think about that before replacing with between.
I also strongly suggest you do not write dates like in your example, because it is ambiguous and will bite you if you are working in a system used in multiple countries.
YYYY-MM-DD is the right way to go, or use convert and be explicit about the format code.
If you tried '12-31-12' in Australia (and many other places), it will error because it will be interpreted as dd-mm-yy and there are not 31 months.
March 6, 2015 at 6:27 am
omarlopez7 (3/6/2015)
In that case not work, i says if you us on the where section like this.Select * from table
where date between '12-12-12' and '12-31-12'
I do not understand your point.
BETWEEN is usually a flawed condition when checking dates because it includes its lower and upper bounds.
x BETWEEN A and B means
A <= x <= B
If you want to pick all dates from 1/1/2014 to 31/3/2014, for example,this is the way to do it:
Date >= '20140101' and Date < '20140401'
Note the absence of an equals sign in the second condition.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 6, 2015 at 6:37 am
davoscollective (3/6/2015)
omarlopez7 (3/6/2015)
In that case not work, i says if you us on the where section like this.Select * from table
where date between '12-12-12' and '12-31-12'
Between is inclusive of the start and end dates, that was the point. The original code does not include the end date. You need to think about that before replacing with between.
I also strongly suggest you do not write dates like in your example, because it is ambiguous and will bite you if you are working in a system used in multiple countries.
YYYY-MM-DD is the right way to go, or use convert and be explicit about the format code.
If you tried '12-31-12' in Australia (and many other places), it will error because it will be interpreted as dd-mm-yy and there are not 31 months.
Actually, this is not the best advice either 🙂
The most universal literal date format is 'YYYYMMDD' – no hyphens. See here for more info.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 6, 2015 at 6:41 am
Of course i'm agree with you, but i write the date like i do just for example but i know how you write is the correct way.
March 6, 2015 at 11:11 am
Timely repost. I was just creating my first-ever sp involving dynamic SQL this week when I saw this.
My scenario wasn't nearly as complex, but this article (with appropriate hat tips to Gail's and Erland's blogs) was intrumental in helping me understand how to do it correctly. I've read far too many articles that talk about avoiding SQL injection in dynamic SQL but never showing how.
____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
March 6, 2015 at 2:41 pm
Can't express my agreement strongly enough with the following:
I really hate it when I have to wade through what I’d call “stream of consciousness” or write-only SQL. This is when it is appropriate to reinstate firing squads as appropriate punishment.
March 8, 2015 at 5:41 pm
omarlopez7 (3/6/2015)
you can replace this part of the query"Last_Status_DT >= '2013-10-25'
AND Last_Status_DT < '2013-10-29';"
For this one
"Last_Status_DT between '2013-10-25' and '2013-10-29';"
And the code are little more simple.
I have to agree with the others that can along here to advise against this approach.
The dates in question are DATETIME and the interval is supposed to be open-ended, which basically means you are looking for a status code greater than or equal the start date but less than the end date. BETWEEN doesn't give you that. Look at the code that constructs the end date to understand why (I believe that it is even explained in the article).
There are a few seasoned posters to this forum that would probably agree that the method that I used is a "SQL Best Practice" (I do for whatever that is worth).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 8, 2015 at 5:43 pm
lshanahan (3/6/2015)
Timely repost. I was just creating my first-ever sp involving dynamic SQL this week when I saw this.My scenario wasn't nearly as complex, but this article (with appropriate hat tips to Gail's and Erland's blogs) was intrumental in helping me understand how to do it correctly. I've read far too many articles that talk about avoiding SQL injection in dynamic SQL but never showing how.
I'm glad you found it useful. While Gail and Erland focused more on the why, I tried to focus on the how, thus (hopefully) giving a cookbook approach with a template applicable to a wide variety of conditions. In fact, I've written so many of these things by now myself, that's exactly how I use it.
Dynamic search stored procs - ya gotta love 'em!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 8, 2015 at 5:45 pm
marcia.j.wilson (3/6/2015)
Can't express my agreement strongly enough with the following:I really hate it when I have to wade through what I’d call “stream of consciousness” or write-only SQL. This is when it is appropriate to reinstate firing squads as appropriate punishment.
That was a pretty strong statement on my part, for which I have no real regrets. Of course, perhaps a little less severe would be to use Jeff Moden's frozen pork chop launcher. I suppose it depends on whether you think the intended target will repeat their actions even after being warned. 😛
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 7, 2015 at 11:46 pm
Hi Dwain ,
Very good article.
I read the article,
I didn't read the whole discussion that is going on.
I have one doubts.
I have requirement where I join table depending upon condition like in your example.
But there is another requirement where output columns from various tables are always fixed,no matter what the where condition is.
Only where condition are dynamic. Say @FromDate can be null or have value ,@Consignment_No can have value or it can be null.
In this situation,dynamic Sql like in your example is good or not.
Or simple we can write query like,
Select t1.*,t2.*,t3.*
From Table1 t1 inner join Table2 t2 on t1.id=t2.id
inner join Table3 t3 on t1.col=t3.col
where (((@FromDate is null) or (t1.FromDate>=@FromDate))
and ((@Consignment_No is null or t2.Consignment_No=@Consignment_No)))
Notice in my example, output column from various source are always same,only where condition are dynamic.
In such situation,should I use dynamic query like in your exmple or my above example is ok.
Other thing remaining constant like indexes are there in place.
Please reply .
[font="Verdana"]Regards
Kumar Harsh[/font]
Viewing 15 posts - 31 through 45 (of 45 total)
You must be logged in to reply to this topic. Login to reply