Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

query that works different inside a SP

diegonzabdiegonzab Member Posts: 6
Hello:
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,

(PD: sorry if my english is not too good)
Sign In or Register to comment.