Author |
Topic |
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-08-17 : 08:49:26
|
A friend of mine was doing some sql stuff and stumbled accross this:http://vidmar.net/weblog/archive/2006/08/17/3044.aspxIs this a bug or a "feature"?because behavior like this is weirding me out!Go with the flow & have fun! Else fight the flow blog thingie: http://weblogs.sqlteam.com/mladenp |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-17 : 08:59:31
|
No, it is not a bug.When ID is 3 or more, the SET operation for AnotherID does not happen. Thus AnotherID still has old value.Peter LarssonHelsingborg, Sweden |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-08-17 : 09:02:17
|
ok yes... but shouldn't the declare part clear the previous value?you can't do this in c++ or c# or any other language i know of.Go with the flow & have fun! Else fight the flow blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-17 : 09:04:21
|
But it is strange, since you can reproduce the bevahiour without a cursor.DECLARE @t TABLE (Id int)INSERT INTO @t VALUES (1)INSERT INTO @t VALUES (2)INSERT INTO @t VALUES (3)INSERT INTO @t VALUES (4)DECLARE @Id intselect @id = min(id) from @tWHILE @id is not nullBEGINPRINT 'Id = ' + ISNULL(CAST(@Id AS varchar), 'NULL')DECLARE @AnotherId intIF @Id <= 2 SET @AnotherId = @Id PRINT 'AnotherId = ' + ISNULL(CAST(@AnotherId AS varchar), 'NULL')select @id = min(id)from @twhere id > @idEND Peter LarssonHelsingborg, Sweden |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-08-17 : 09:06:31
|
yes, cursor is irrelevant Go with the flow & have fun! Else fight the flow blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-17 : 09:08:11
|
As you would expect,declare @DECLARE @AnotherId intlast, after end, gives an errorPeter LarssonHelsingborg, Sweden |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-08-17 : 09:10:26
|
yup tried that too, putting it in front of course works It's just weird...Go with the flow & have fun! Else fight the flow blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-17 : 09:13:05
|
More simplified...DECLARE @x int--DECLARE @AnotherId intselect @x = 3while @x > 0BEGIN DECLARE @AnotherId int SET @AnotherId = @x-- DECLARE @x int-- DECLARE @AnotherId int PRINT 'AnotherId = ' + ISNULL(CAST(@AnotherId AS varchar), 'NULL') select @x = @x - 1END Uncommenting any of the two rows gives an error.The thing that comes to my mind is that a WHILE LOOP creates a scope if its own.Peter LarssonHelsingborg, Sweden |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-08-17 : 09:17:20
|
i don't think so.... because then putting DECLARE @AnotherId int in front of the while shouldn't workin the while's scope.variables are valid only in a scope.Go with the flow & have fun! Else fight the flow blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-17 : 09:20:22
|
Did you see my last post? Uncommenting --DECLARE @AnotherId int gives an error. Exactly as it supposed to do if WHILE creates a scope.Also declaring @AnotherID twice in the WHILE gives an error.Peter LarssonHelsingborg, Sweden |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-08-17 : 09:27:41
|
uncommenting any of the 2 rows in your code should produce an error and it correctly does it.The problem here is acctually the order of declaring variablesin C# it's like so:int i = 0;while(i<4){ int k; // here k will be 0 every time the loop goes in it. it will be redeclared, sql's doesn't k = i i++} and i'd assume that sql's while would behave similarly.Go with the flow & have fun! Else fight the flow blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-17 : 09:32:57
|
Neither does VB redeclare variables.SQLDECLARE @x intselect @x = 5while @x > 0BEGIN DECLARE @AnotherId int if @x % 2 = 0 SET @AnotherId = isnull(@AnotherId, 5) - 1 PRINT 'AnotherId = ' + ISNULL(CAST(@AnotherId AS varchar), 'NULL') select @x = @x - 1END Output from SQLAnotherId = NULLAnotherId = 4AnotherId = 4AnotherId = 3AnotherId = 3 VBPrivate Sub Command1_Click()Dim x As Longx = 5While x > 0Dim s As LongIf x Mod 2 = 0 Then s = s - 1 Debug.Print x, s x = x - 1WendEnd Sub Output from VB is 5 0 4 -1 3 -1 2 -2 1 -2 Peter LarssonHelsingborg, Sweden |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-08-17 : 09:49:08
|
ok... well i learned something new today:in c# this won't build:int i = 0;while(i<4){ int k; if (i < 2) { k = i; } Console.WriteLine("i: " + i.ToString()); Console.WriteLine("k: " + k.ToString()); // errors out with: Use of unassigned local variable 'k' i++;} this will build and going through with the debugger it behaves the same as VB and SQL.int i = 0;while (i < 4){ int k; if (i < 2) { k = i; } i++;} Thinking about it it does make some sense with memory management etc, but it's not really intuitive if you ask me.Go with the flow & have fun! Else fight the flow blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-08-17 : 11:30:01
|
Looks like it is just a matter of the DECLARE occuring before a statement the uses the declared variable.Obviously the 1=2 condition in the code below is not true, but the variable is declared anyway.if 1=2 begin declare @x int set @x = 1 Print '1=2' endselect x=@xResult:x ----------- NULL(1 row(s) affected) CODO ERGO SUM |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-08-17 : 11:33:49
|
well i've found a definite answer.A variable is declared only once and every manipulation of it must be done with set/select.any subsequent run over the same declare @var int line does nothing as the variable is already declared.all variables are declared in the batch like in VB (ignores if's)and not like in C# (doesn't ignore if)Go with the flow & have fun! Else fight the flow blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-08-17 : 11:36:27
|
>>Obviously the 1=2 condition in the code below is not true, but the variable is declared anyway.Then isnt this a strange?MadhivananFailing to plan is Planning to fail |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-08-17 : 11:39:17
|
no it's not anymore Go with the flow & have fun! Else fight the flow blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-17 : 11:42:53
|
I think it is a "feature" to help programmers if they declare variables in the wrong place.Peter LarssonHelsingborg, Sweden |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-08-17 : 11:48:24
|
well as i dislike VB i'll call it a bug Go with the flow & have fun! Else fight the flow blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-08-17 : 11:56:06
|
quote: Originally posted by spirit1 well as i dislike VB i'll call it a bug Go with the flow & have fun! Else fight the flow blog thingie: http://weblogs.sqlteam.com/mladenp
Planning on opening a case with Microsoft to get a bug fix?CODO ERGO SUM |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-08-17 : 12:26:42
|
nope someone who likes VB will argue it's a feature... so what's the use...just knowing this is fine by me.Go with the flow & have fun! Else fight the flow blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
Next Page
|