A different evaluation order in the SET clause of

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

A different evaluation order in the SET clause of

Author

Topic

jirpun
Starting Member

2 Posts

Posted - 2010-02-02 : 08:32:23

Hi all,
I have registered a differ behavior between the SQL server 2000 and 2008.
The next sample below returns different results, the rows are differently numbered.

In the SQL server 2008:
id_test_table some_code
------------- ---------
0 A
1 B
2 C
3 D
4 E

in the SQL server 2000:
id_test_table some_code
------------- ---------
1 A
2 B
3 C
4 D
5 E

Does anybody know a reason, an explanation?
Thanks to all.
Jiri

create function dbo.TheSameInt(@pInt int)
returns int
as
begin
return @pInt
end
go

create table #test_table(
id_test_table INT,
some_code CHAR(1))
go

insert into #test_table values (0, 'A')
insert into #test_table values (0, 'B')
insert into #test_table values (0, 'C')
insert into #test_table values (0, 'D')
insert into #test_table values (0, 'E')
go

declare @v_counter int
set @v_counter = 0

update #test_table
set id_test_table = dbo.TheSameInt(@v_counter),
@v_counter = @v_counter + 1

select * from #test_table

drop table #test_table
go

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-02 : 08:54:41

It is a behavioural change from one version to another version
In version 2000, variable is calculated and used no matter which order it is. But in version 2005 onwards the order is forced. So your code should be

declare @v_counter int
set @v_counter = 0

update #test_table
set @v_counter = @v_counter + 1,
id_test_table = dbo.TheSameInt(@v_counter)

select * from #test_table

Madhivanan

Failing to plan is Planning to fail

jirpun
Starting Member

2 Posts

Posted - 2010-02-02 : 09:13:43

quote:
Originally posted by madhivanan

It is a behavioural change from one version to another version
In version 2000, variable is calculated and used no matter which order it is.

Ok, the solution is clear.

But is this behavioural change documented anywhere?

Jiri

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-02 : 09:19:21

Must be somewhere here
http://msdn.microsoft.com/en-us/library/ms143359.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources