Table Name in Variable - Create Statement

[eric]

Is it possible to have part of a table name used in a CREATE statement
contained in a variable? Here's what I'd like to do, although
obviously the syntax of this isn't quite right or I wouldn't be here
asking:

DECLARE @TblPrefix char (3)
SET @TblPrefix = 'tst'
CREATE TABLE @TblPrefix + TestTable (col1 int)

The point there is to have a table named tstTestTable

The reason I need to do this is that my ISP will only give me one
database to work with and I'd like to have two copies of the
application I'm developing running at the same time. So I'd like to
run the sql script that creates the tables with TblPrefix set to "dev"
and then run it again with TblPrefix set to "liv"

thanks
eric

[Simon Hayes]

"eric" <en***@m-net.arbornet.org> wrote in message
news:cc*************************@posting.google.co m...

Is it possible to have part of a table name used in a CREATE statement
contained in a variable? Here's what I'd like to do, although
obviously the syntax of this isn't quite right or I wouldn't be here
asking:

DECLARE @TblPrefix char (3)
SET @TblPrefix = 'tst'
CREATE TABLE @TblPrefix + TestTable (col1 int)

The point there is to have a table named tstTestTable

The reason I need to do this is that my ISP will only give me one
database to work with and I'd like to have two copies of the
application I'm developing running at the same time. So I'd like to
run the sql script that creates the tables with TblPrefix set to "dev"
and then run it again with TblPrefix set to "liv"

thanks
eric

If you check the syntax in Books Online, you'll see that CREATE TABLE
doesn't accept variables for the table name, so you need to use dynamic SQL:

exec('create table ' + @tblprefix + 'TestTable (col1 int)')

What you're doing is a reasonable case for a dynamic solution, but beware of
overusing it. See here for some more discussion:

http://www.sommarskog.se/dynamic_sql.html#Cre_tbl

You might also want to consider creating your objects with SQLDMO and
something like Perl, VB, C# etc., as it's often easier to set a .Name
property on the fly using a client language than it is to construct numerous
dynamic SQL statements.

Simon

[Steve Troxell]

eric wrote:

Is it possible to have part of a table name used in a CREATE statement
contained in a variable?

Nope. You'll have to build your SQL statement in a varchar variable and then
execute it by calling exec.

declare @TblPrefix char (3)
set @TblPrefix = 'tst'
exec('create table ' + @TblPrefix + 'TestTable (col1 int)')

--
Steve Troxell

[eric]

"Simon Hayes" <sq*@hayes.ch> wrote in message news:<41**********@news.bluewin.ch>...

"eric" <en***@m-net.arbornet.org> wrote in message
news:cc*************************@posting.google.co m...

Is it possible to have part of a table name used in a CREATE statement
contained in a variable?

If you check the syntax in Books Online, you'll see that CREATE TABLE
doesn't accept variables for the table name, so you need to use dynamic SQL:

That's what I was afraid of. Dynamic SQL is going to be harder to
read and maintain so I think I'd better keep my original scripts
laying around.

BTW, of course I checked the books online. I'm not sure why you'd
even mention that.