Query that works different inside a SP
-
Hello everyone:
I have what, I think, is a very weird problem: I have a query that works just fine if I run it alone, but when I run it as part of a sored proc, it generates much less rows.
The only difference is that I'm including some 'variable parameters' in the query when it runs in the SP. What I mean is something like this:
1. If I execute this query:
SELECT A1, A2, B1, ...
FROM A INNER JOIN B
WHERE A.Date1 BETWEEN '2004/08/01' AND '2004/08/30'
AND B.B2 > 200408
Then I get, lets say, 10000 rows
2. If, having this Stored Proc:
SP_XX (@param1 datetime, @param2 datetime, @paramInt int)
AS
...
SELECT A1, A2, B1, ...
FROM A INNER JOIN B
WHERE A.Date1 BETWEEN @param1 AND @param2
AND B.B2 > @paramInt
...
And then I execute this SP in this way:
EXEC SP_XX '2004/08/01', '2004/08/30', 200408
Then I get only 1000 rows, ( ¡9000 rows less! )
I've tried already many things, like changing the 'datetime' params for 'varchar(10)' params in the SP and then execute it and the result is the same, (this is because I think the problem is with the datetime params of the SP, but I'm not sure). I also did a conversion from varchar to datetime for calling the SP, [e.g:
EXEC SP_XX Convert(datetime, '2004/08/01'), Convert(datetime, '2004/08/30'), 200408 ],
and the result is still the same. I have also executed the SP passing the datetime parameters in many different formats: yyyy/mm/dd, yyyymmdd, mm/dd/yyyy, etc...
If anyone has any idea, please let me know, since I've been struggling with this for already too long.
Thanks in advance
-
Try to include time part in your params i.e. '2004/08/08 00:00:00' (both in SP and in your query) - then run it and check how many rows returned.
This may help - not sure why (I think because "BETWEEN" operator is a little bit wired when used with dates)
Alex
-
This is a string: '2004/08/01'
This is a datetime value: @param1 datetime
They are NOT the same thing, even if you enter 2004/08/01 for the parameter.
The difference is that datetime value has a time. So your WHERE with the parameters is really:
WHERE A.Date1 BETWEEN '2004/08/01 00:00:00.000' AND '2004/08/30 00:00:00.000' AND B.B2 > 200408
Try running your first script as is, and then with the WHERE clause above. See if there is a difference. There should be, as Alex points out, there is an issue with BETWEEN that is sometimes overlooked. 2004/08/30 00:00:00 is not BETWEEN those two times.
WHERE mynumber BETWEEN 1 AND 3
is not the same as
WHERE mynumber >= 1
AND mynumber <= 3
mynumber of 2 will be returned by both queries, but mynumber of 1 or 3 will only be returned by the last WHERE.
-SQLBill
-
If you aren't interested in the time period in the where clause you can convert your A.Date1 to remove the time. This should then remove the problem of times after midnight not matching the between statement e.g.
where cast(convert(varchar(10), a.Date1, 112) as datetime) between @param1 and @param2
This will take the date and time and then replace the time with 00:00:00.000 and will then match when only dates are passed on the parameters.
regards
Andrew Barton
-
Hi,
you didn't mention any info about the data type of underlying tables. What are the data types of columns A.Date1 and B.B2 - datetime and integer, or varchar?
Did you try it the other way round, i.e. modifying not the SP, but the query? I would try this: DECLARE the '2004/08/01', '2004/08/30' and 200408 as variables before the query and then use these variables in the WHERE condition. What is the result? Is it the same as in the stored procedure? August has 31 days... make sure you don't have 2004/08/31 in one of the two cases (it seems to me that this is just a piece of a larger code, so maybe you are taking these values over from somewhere?)
I'm quite surprised by the last part of SQLBill's answer, since I seemed to know that BETWEEN includes both borders... that is, BETWEEN 1 AND 3 are all three values : 1, 2 and 3. I may have missed something in the post, so that it was meant differently than as I understood it, but I'm quite confident about the inclusion of borders.
The datetime issue with BETWEEN (as well as <= or =) is IMHO different. Problems start, if you specify the ending date of condition without time part, while the tested column /or supplied value/ contains time. Then the program considers condition as e.g. <= 2004.08.30 00:00:00 ... and that means that no entries from this day are included in the result (their date is 2004.08.30 00:00:01 or higher -> greater than border). To avoid this, make sure that the condition is specified like this:
BETWEEN '2004.08.01' AND '2004.08.30 23:59:59.999'
or better, strip the time part off the value before comparing it, while leaving condition written the way you have it, w/o time part (see Andrew Barton's post). Well, maybe not better... once you start doing this, it can happen, that indexes are not used and the query runs slow. Test both.. it depends :-).
HTH, Vladan
-
Diego,
Please do not double post.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=134873
-SQLBill
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply