|
|
Posted by David Portas on 04/16/06 13:49
Peter wrote:
> Hello all,
>
> I have the following t-sql batch:
>
>
> create procedure stp_test
> (
> @p_date1 as datetime = null,
> @p_date2 as datetime = null
> )
> as begin
> set @p_date1 = isnull(@p_date1, <some expression>)
> set @p_date2 = isnull(@p_date2, <some other expression>)
>
> select
> <a lot of columns>
> from
> <some table>
> inner join <some other table> on <expression>
> inner join <dirived table> on <expression>
> where
> date1 <= @p_date1 and
> date2 <= @p_date2 and
> (
> date1 >= @p_date1 or
> date2 >= @p_date2
> )
> end
> go
>
> exec stp_test
>
> This gives a WRONG resultset.
>
> When I replace the variables with hardcoded values in the right format, the
> returned result set is CORRECT, as follows
>
> where
> date1 <= 'hard coded date value 1' and
> date2 <= 'hard coded date value 2' and
> (
> date1 >= 'hard coded date value 1' or
> date2 >= 'hard coded date value 2'
> )
>
> When I elimate the derived table with a temporary table, the returned result
> set is CORRECT
>
> When I store the parameters in a local variable, and use the local variable,
> the returned result set is CORRECT, as follows
>
> create procedure stp_test
> (
> @p_date1 as datetime = null,
> @p_date2 as datetime = null
> )
> as begin
> declare @l_date1 datetime
> declare @l_date2 datetime
>
> set @l_date1 = @p_date1
> set @l_date2 = @p_date2
>
> set @l_date1 = isnull(@l_date1, <some expression>)
> set @l_date2 = isnull(@l_date2, <some other expression>)
>
> select
> <a lot of columns>
> from
> <some table>
> inner join <some other table> on <expression>
> inner join <dirived table> on <expression>
> where
> date1 <= @l_date1 and
> date2 <= @l_date2 and
> (
> date1 >= @l_date1 or
> date2 >= @l_date2
> )
> end
> go
>
> When I put less columns in the select list, the returned result set is
> CORRECT, it doesnt make sense wich columns I remove from the select list.
>
> The tables are not small (500.000 rows) and also is the result set. I use
> this construction elsewhere, on other table combinations, but dont have
> problems. So the content of the data makes difference.
>
> Seems to me as a bug.
>
> My question is: Can I say the derived table is instable in SQL server and
> causes the problem of the wrong result set here?
>
> Peter
Please could you post some working code to reproduce the problem
(CREATE TABLE and INSERTs included). Also tell us what version, edition
and service pack you are using. That's essential information if you
think you've found a bug. "Wrong result" is also not a good description
of the problem. Show us or explain what result you expected and what
you actually got.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|