ALTER table command

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,
PaulYou 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" <psampson@.uecomm.com.au> wrote in message
news:1061964209.790366@.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|||"Paul Sampson" <psampson@.uecomm.com.au> wrote in message news:<1061964209.790366@.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|||Thanks Simon - a common suggestion and one that I'll be sure to remember.

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:60cd0137.0308270337.2bbb79e4@.posting.google.c om...
> "Paul Sampson" <psampson@.uecomm.com.au> wrote in message
news:<1061964209.790366@.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