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
-
I have no answer. Here is something to try.
Are you EXECing the SP from the same Query Analyzer window as the normal SQL? If not it is possible your connection has assigned a ROWCOUNT setting of 1000.
If you generate the ALTER or CREATE script from Query Analyzer it will show you the QUOTED IDENTIFIER and ANSI_NULLS settings being used for the SP which override the connection settings. If the differ they can change your results. I did some playing, though, and I don't think its an ANSI_NULLS issue.
Within Query Analyzer, try declaring variables, assigning values, and running the same SQL. If you get limited results you can start replacing some variables with constants to determine if its the dates vs the number causing the problem.
For the purpose of this testing I'd use either a TOP 1100 since it is large enough to tell you whether or not the results are truncated or else use a COUNT instead (but verify the SP still fails with either the TOP 1100 or COUNT as a baseline).
-
I am not seeing that you are joining on any fields within your inner join reference?
-
Diego,
Please do not double post.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=134874
-SQLBill
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply