ALTER table command

[Paul Sampson]

Hi,

I'm trying to run the ALTER TABLE command using a dynamic string for the
table, like so:

DECLARE @TableName CHAR
SET @TableName = 'Customers'
ALTER TABLE @TableName
ADD ...blah

Is this possible? We know this works:

ALTER TABLE Customers ADD ...blah

It looks like I need a way to convert the CHAR value to a literal or perhaps
even a table ID?

Thanks in advance,
Paul

[oj]

You would need to use dynamic query...

e.g.
exec('alter table '+@tb+' add blah')
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

"Paul Sampson" <ps******@uecomm.com.au> wrote in message
news:10***************@proxy.uecomm.net.au...

Hi,

I'm trying to run the ALTER TABLE command using a dynamic string for the
table, like so:

DECLARE @TableName CHAR
SET @TableName = 'Customers'
ALTER TABLE @TableName
ADD ...blah

Is this possible? We know this works:

ALTER TABLE Customers ADD ...blah

It looks like I need a way to convert the CHAR value to a literal or perhaps even a table ID?

Thanks in advance,
Paul

[Simon Hayes]

"Paul Sampson" <ps******@uecomm.com.au> wrote in message news:<10***************@proxy.uecomm.net.au>...

Hi,

I'm trying to run the ALTER TABLE command using a dynamic string for the
table, like so:

DECLARE @TableName CHAR
SET @TableName = 'Customers'
ALTER TABLE @TableName
ADD ...blah

Is this possible? We know this works:

ALTER TABLE Customers ADD ...blah

It looks like I need a way to convert the CHAR value to a literal or perhaps
even a table ID?

Thanks in advance,
Paul

You can use dynamic SQL:

declare @tablename sysname
set @tablename = 'Customers'
exec('alter table dbo.' + @tablename + ' add ...')

See here for more information on dynamic SQL:

http://www.algonet.se/~sommar/dynamic_sql.html

By the way, if you declare a variable as CHAR without a length, it
will default to CHAR(1). For object names, sysname is a better choice.

Simon

[Paul Sampson]

Thanks Simon - a common suggestion and one that I'll be sure to remember.

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:60**************************@posting.google.c om...

"Paul Sampson" <ps******@uecomm.com.au> wrote in message

news:<10***************@proxy.uecomm.net.au>...

Hi,

I'm trying to run the ALTER TABLE command using a dynamic string for the
table, like so:

DECLARE @TableName CHAR
SET @TableName = 'Customers'
ALTER TABLE @TableName
ADD ...blah

Is this possible? We know this works:

ALTER TABLE Customers ADD ...blah

It looks like I need a way to convert the CHAR value to a literal or perhaps even a table ID?

Thanks in advance,
Paul

You can use dynamic SQL:

declare @tablename sysname
set @tablename = 'Customers'
exec('alter table dbo.' + @tablename + ' add ...')

See here for more information on dynamic SQL:

http://www.algonet.se/~sommar/dynamic_sql.html

By the way, if you declare a variable as CHAR without a length, it
will default to CHAR(1). For object names, sysname is a better choice.

Simon