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
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
Comment