Adding Column to table

1 Oct 2005 1:24 PM

Stephen K. Miyasato

I'm writing a procedure to add column to the database but need to check if
the column already exist.

How does one check to see if it exist. The reason is that if I have multiple
statements adding columns then it seem that the procedure will error out
and not go to the second statement. So if rowguid already exits in the table
user, it will not do the next statement.

Thanks for your help

Stephen K. Miyasato

Alter table users ADD [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
CONSTRAINT [DF__Users__rowguid__4C220BCC] DEFAULT (newid())

Alter table users ADD User_Type smallInt

1 Oct 2005 1:51 PM

David Portas

Here's one method:

IF COLUMNPROPERTY(OBJECT_ID('users'),'rowguid','AllowsNull') IS NULL
ALTER TABLE users ADD rowguid UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL
CONSTRAINT [df_users_rowguid] DEFAULT (NEWID())

However, I don't recommend you do this in a stored procedure. One problem is
that you will still get errors if you reference the column in static code in
the same proc. That's because the column name has to be resolvable at
compile time and not just when a statement is executed. Another issue is the
disproportionate effort required to test, debug and maintain systems that
modify schema at runtime. Schema mods should happen at install time so I
can't think of many good reasons to do this in a proc.

--
David Portas
SQL Server MVP
--

Show quote

"Stephen K. Miyasato" <miya***@flex.com> wrote in message
news:eJtl9toxFHA.900@TK2MSFTNGP11.phx.gbl...
> I'm writing a procedure to add column to the database but need to check if
> the column already exist.
>
> How does one check to see if it exist. The reason is that if I have
> multiple statements adding columns then it seem that the procedure will
> error out and not go to the second statement. So if rowguid already exits
> in the table user, it will not do the next statement.
>
> Thanks for your help
>
> Stephen K. Miyasato
>
>
>
> Alter table users ADD [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
> CONSTRAINT [DF__Users__rowguid__4C220BCC] DEFAULT (newid())
>
> Alter table users ADD User_Type smallInt
>