conditional takes UPDATE before ALTER TABLE? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

conditional takes UPDATE before ALTER TABLE?

Author

Topic

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-30 : 15:00:40

Hi, I'm trying to use a conditional statement in QA in which I first alter a table, then I attempt to update the column I just created. When I run the code below, SQL returns error saying "invalid column name field2" suggesting to me that the UPDATE statement is somehow being executed before the ALTER TABLE statement. I know the UPDATE code works -- I've tried executing this code in isolated blocks and the update works -- it just doesn't seem to work between BEGIN...END statement block. do i need to tell the script to send the alter table first, somehow? thx, code sample below...

IF EXISTS (SELECT M2 FROM master

WHERE M2 = 2)

BEGIN

ALTER TABLE #holder
ADD field2 float NULL

UPDATE #holder
SET #holder.field2 = master.field2
from #holder, master
WHERE master.M1 = #holder.M1_ID

END

ELSE

BEGIN

print 'over and out'

END

cas_o
Posting Yak Master

154 Posts

Posted - 2004-04-30 : 15:13:55

Does master definitely have a field2 column?

or maybe it's field_2 or feild2 or something silly like that?

The Alter table statement looks fine to me.

Also I assume there is a create table #holder... statement above this If block?

;-]... Quack Waddle

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-30 : 15:24:06

Yes, master definitely has a field2 column. yes there is a create table for #holder.

The odd thing is that if I break the code up and run the script only up to:

BEGIN

ALTER TABLE #holder
ADD field2 float NULL

END

ELSE

BEGIN

print 'over and out'

END

...then I execute the UPDATE block manually during the same session, the update works...

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-30 : 15:56:57

When the objects for the batch are resolved #holder does not have a field2 and so the following statement fails - the optimiser does not include alter tables in it's evaluation.
You need to execute the next statement in another batch - dynamic sql or a called sp.
see
http://www.nigelrivett.net/AccessTempTablesAcrossSPs.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cas_o
Posting Yak Master

154 Posts

Posted - 2004-04-30 : 16:01:46

Or just create #holder with a field2 column in the beginning and be done with it, rather than add it conditionaly later.

;-]... Quack Waddle

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-30 : 16:25:37

good points all. I was adding the fields conditionally to make the output "pretty", i.e. I wanted number of fields to = number of actual variables, but I see now that I can just make a 100 col x 100 row box to handle my data and pluck out only the data I need.

thx

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-30 : 19:03:23

You can also:

IF EXISTS (SELECT M2 FROM master WHERE M2 = 2)

BEGIN

BEGIN TRANWRAPPER

BEGIN TABLETRAN

ALTER TABLE #holder
ADD field2 float NULL

COMMIT TABLETRAN

BEGIN UPDATETRAN

UPDATE #holder
SET #holder.field2 = master.field2
from #holder, master
WHERE master.M1 = #holder.M1_ID

COMMIT UPDATETRAN

COMMIT TRANWRAPPER

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-01 : 01:03:23

Few problems with that
Think it is trying to do begin tran but missing the tran.

Transactions do not effect the batch so this will still have the same problem in that field2 will not exist when the update statement is compiled.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-02 : 19:17:12

Yeah, you're right. He'd have to put a batch terminator between the alter and the update. Sorry about that.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources