Do While Loop?

[iamtrying]

Hello, a co-worker sent this script to me asking for help. I am unable to assist. Can someone decipher the problem with this script?

DECLARE @out AS int, @in AS int, @transnum AS int
SET @out = 1
SET @in = 1
SET @transnum = 1 BEGIN WHILE @out < 801 BEGIN WHILE @in < 5
Select master,transact
UPDATE transact
SET transact.id = @transnum, transact.ss = master.[Social Security], transact.tfromdate = '1/1/2009', transact.ttodate = '1/1/2009',
transact.ttype = @in, transact.thours = 0
FROM master
SET @in = @in + 1
SET @transnum = @transnum + 1 END
SET @in = 1
SET @out = @out + 1 END;

SQL Execution Error.

Executed SQL statement: DECLARE @out AS int, @in AS int, @transnum AS int SET @out = 1 SET @in = 1 SET @transnum = 1 BEGIN WHILE @out < 801 BEGIN WHILE @in < 5 SELECT master,transact UPDATE transact SET transact.id = @transnum, transact.ss = master.[Social Security], transa...
Error Source: .Net SqlClient Data Provider
Error Message: Invalid column name 'master'.

Invalid column name 'transact'.

 

[bborissov]

That is because you (your co-worker) missed some BEGIN END blocks in the WHILE LOOP:

[COLOR=blue]DECLARE[/color] @out [COLOR=blue]AS[/color] [COLOR=blue]int[/color], @in [COLOR=blue]AS[/color] [COLOR=blue]int[/color], @transnum [COLOR=blue]AS[/color] [COLOR=blue]int[/color]
[COLOR=blue]SET[/color]              @out = 1
[COLOR=blue]SET[/color]              @in = 1
[COLOR=blue]SET[/color]              @transnum = 1
[COLOR=blue]BEGIN[/color] 
   [COLOR=blue]WHILE[/color] @out < 801
         [COLOR=blue]BEGIN[/color] 
              [COLOR=blue]WHILE[/color] @in < 5
                    [COLOR=blue]BEGIN[/color]
                        [COLOR=blue]Select[/color] master,transact
                        [COLOR=blue]UPDATE[/color] transact
                               [COLOR=blue]SET[/color] transact.id = @transnum,
                                   transact.ss = master.[Social Security],
                                   transact.tfromdate = [COLOR=red]'20090101'[/color],
                                   transact.ttodate = [COLOR=red]'20090101'[/color],
                                   transact.ttype = @in,
                                   transact.thours = 0
                         [COLOR=blue]FROM[/color] master
                         [COLOR=blue]SET[/color] @in = @in + 1
                         [COLOR=blue]SET[/color] @transnum = @transnum + 1
                    [COLOR=blue]END[/color]
            [COLOR=blue]SET[/color] @out = @out + 1
            [COLOR=blue]SET[/color] @in = 1
         [COLOR=blue]END[/color]
[COLOR=blue]END[/color]

NOT TESTED!!!!!!!!

Check also how I changed the datetime format.
I use so called ISO format (YYYYMMDD). This format is unambitious and SQL Server understands it no matter what is your current language or DATEFORMAT.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Qik3Coder]

spelling errors are the worst:


Select master,transact

should be:


Select master.transact

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[bborissov]

O!
When you said this I saw it
I know start to wondering: Why it is doing here?
It didn't do any job, just slow down the execution )
No WHERE clause, nothing, just a pure SELECT w/o any FROM clause )

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[iamtrying]

Excellent nice, also he was trying to update an empty table. He must use insert correct?

 

[Qik3Coder]

I wouldn't let him near my db...

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[iamtrying]

I hear ya, but no one seems to care

 

[bborissov]

Yes, he should.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.