ADD COLUMN - SQL Server Forums

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

Transact-SQL (2000)

ADD COLUMN

Author

Topic

bsav
Starting Member

7 Posts

Posted - 2004-08-26 : 14:22:11

I think I've narrowed my problem down. I do an "alter table add column", then the next statement tries to do an "update" to that column. The update says the column doesn't exist though. Here's the script:

if(1=1)
BEGIN

ALTER TABLE CCAuths ADD CCNumTmp varbinary(32) DEFAULT 0 NOT NULL
UPDATE CCAuths SET CCNumTmp = CONVERT(varbinary(32), CCNum)

END
GO

BSav

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-26 : 14:36:58

try putting GO between Alter and update.

Go with the flow & have fun! Else fight the flow :)

bsav
Starting Member

7 Posts

Posted - 2004-08-26 : 14:41:53

Yea, can't get that to fly syntax wise:

Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near 'NULL'.
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'END'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-26 : 14:43:13

ALTER TABLE CCAuths ADD CCNumTmp varbinary(32) DEFAULT 0 NOT NULL
GO
UPDATE CCAuths SET CCNumTmp = CONVERT(varbinary(32), CCNum)
GO

Tara

bsav
Starting Member

7 Posts

Posted - 2004-08-26 : 14:46:00

Yes, that's exactly what I did, and got the error mentioned above.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-26 : 14:50:08

ahh that's because of the if

Go with the flow & have fun! Else fight the flow :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-26 : 14:50:56

this should do it:

if(1=1)
BEGIN
alter table customers add bla varchar(200)
end

go

if(1=1)
BEGIN
update customers set bla = ContactName
end

Go with the flow & have fun! Else fight the flow :)

bsav
Starting Member

7 Posts

Posted - 2004-08-26 : 14:54:30

Why does the if cause this problem? What don't I understand?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-26 : 14:55:26

Yep, it's not exactly what you did as you still had the IF. When the GO runs, it breaks out of it.

Tara

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-26 : 14:56:30

as i get it go starts the new batch and if can't be in different batches.

Go with the flow & have fun! Else fight the flow :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-26 : 14:56:57

It causes a problem because the BEGIN doesn't have a corresponding END. Here is how SQL executed it:

IF (1=1)
BEGIN
ALTER
GO

So the above ran and there was no corresponding END.

Tara

bsav
Starting Member

7 Posts

Posted - 2004-08-26 : 15:00:58

Ahhhh! I see! Thanks all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources