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
