|
|
Posted by Peter on 04/16/06 13:59
David,
I am not able now to post insert and the final code. It is too much for this
newsgroup. When I have to put real data, I have to encrypt it also. Maybe
then the behavior als changing too.
I dont get an errormessage. The problem is, not all results are in the
resultset I expected. My expectation is correct, because, if I change
something as i stated, the resultset is like expected.
I use MS SQL Server 2000 sp4. No hotfixes.
Thank you David.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:1145184565.985720.34060@i39g2000cwa.googlegroups.com...
> 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]
|