Wrong Result Set

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