Consider the following TSQL code:

----------------------------
create table #t1 (
  f1 int
)

alter table add f2 int

update #t1
set f2 = 5
----------------------------

Such a code produces the following error message:

Server: Msg 207, level 16, state 1, line 7
Invalid column name 'f2'.

Line 7 is 'update #t1'. What is going on? The field is there, if I write
    select * from #t1
I get the empty table with two fields.

I am using SQL Server product version 8.00.2039 (SP4)

Any advice will be appreciated.

DW.

You have to do things in a separate batch, i.e. put a go between the alter
and the update.

Consider the following TSQL code:

----------------------------
create table #t1 (
  f1 int
)

alter table add f2 int

update #t1
set f2 = 5
----------------------------

Such a code produces the following error message:

Server: Msg 207, level 16, state 1, line 7
Invalid column name 'f2'.

Line 7 is 'update #t1'. What is going on? The field is there, if I write
    select * from #t1
I get the empty table with two fields.

I am using SQL Server product version 8.00.2039 (SP4)

Any advice will be appreciated.

DW.

So I cannot use such a code in one procedure?

In fact I am working on someone else's code, these statements were
originally string variables that were exec'ed. This worked but it was
totally unreadable, so I decided to rewrite it to the pure tsql, but it
stopped working... what can I do? The original code contains many such
constructs.

DW.

BTW.
Putting 'go' after 'alter table' statement does not change anything.
This time update succeeds, but subsequent
    select * from #t1
returns an error code:

Server: Msg 208, Level 16, State 1, Line ...
Invalid object name '#f2'.

Try doing it all inside the CREATE statement - in other words find a way to
lose the ALTER statements. I do, however, sense bigger issues. Maybe you
should elaborate a bit more on what your goals are.


ML

Might be a lot of re-coding. Anyway it seems the only way...


I have answered Tom: I have a big legacy code that I changed from EXECs to
normal form - and it stopped working.

DW.

The procedure is already parsed and while executing the column is not
recognized by sqlserver. You will have to do your update in an Execute
statement because the proc will else fail due to the described problem.
Show quoteHide quote

Why do you have to alter the table?  Why not just create it in one
statement?

So I cannot use such a code in one procedure?

In fact I am working on someone else's code, these statements were
originally string variables that were exec'ed. This worked but it was
totally unreadable, so I decided to rewrite it to the pure tsql, but it
stopped working... what can I do? The original code contains many such
constructs.

DW.

BTW.
Putting 'go' after 'alter table' statement does not change anything.
This time update succeeds, but subsequent
    select * from #t1
returns an error code:

Server: Msg 208, Level 16, State 1, Line ...
Invalid object name '#f2'.

This is a big legacy code - about 1000 lines. The author did this for some
reason... now I have to do something about this.

DW.

I guess asking whether you have access to the original business
specifications and requirements would be stupid... But it really would be
much easier if you could first fully understand the basics of what this code
is supposed to do, rather than trying to guess what it actually does. I bet
you could then start from the scratch and do a much better job at it.

Keep in mind, though, that I haven't seen the entire 1000 lines of this
monster, and that I'm just presuming what dragons might be lurking in there,
considering what you've posted so far. CREATE statements, followed by ALTER
statements in a single procedure reek of lousy or no planning. Keep off that
path.

Well, when you're done, at least you'll know what to expect from the rest of
this guy's masterpieces. Hey, it might even provide several full-time SQL
programmer jobs. :)


ML

p.s. Maybe you can post the whole damn thing on http://www.thedailywtf.com/.

Not good enough.  Just because the original author used bad coding practices
doesn't mean that you should follow in his/her steps.  I'll say like ML -
figure out the specs and the code will flow from that.  Everything else is a
fool's errand.

This is a big legacy code - about 1000 lines. The author did this for some
reason... now I have to do something about this.

DW.

Thanks to all of you.

I have just rewritten the code in such a way that all the fields are created
in a single 'CREATE TABLE'. It did require lots of recoding of 'INSERT'
queries, but I think I succeeded ;-).

I think that the previous programmer's motivation was to create the least
necessary number of fields at the beginning, put some data, then create new
fields and update the new fields only. It did not work normally, so I guess
that might be a reason why he had started putting all the statements into
string variables and executing them using EXECs. It looks like this became
his normal programming style. I can only say that he was a real MASTA, if he
could maintain such a code :-)))). For me, this was totally unreadable.

DW.

Newbee question about permissions and sp_OACreate
Stored Procs vs VIEWS: Seeking Comparison
How to find gaps in sequential key
boolean programming
Creating a trigger using a cursor