variables in a table name - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

variables in a table name

Author

Topic

Cornelius19
Starting Member

30 Posts

Posted - 2008-07-16 : 11:52:29

Hi,

Can I use variables in a table name and if yes how? I have the following code:

declare @MyTable nvarchar(50)
set @MyTable = 'TableXYZ'

CREATE TABLE [@MyTable] (
[id] [bigint] IDENTITY (1, 1) NOT NULL ,
[word] [nvarchar] (255) COLLATE Latin1_General_CS_AS NULL,
[date] [bigint] NULL ,
CONSTRAINT ['PK_'+@MyTable] PRIMARY KEY CLUSTERED
( [id] )
)

The commands are completed successfully but instead of creating a table 'TableXYZ' (as I wanted) it created a table '@MyTable'.

Cornelius

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-16 : 12:21:20

declare @MyTable nvarchar(50)
set @MyTable = 'TableXYZ'

declare @sql varchar(1000)
select @sql =
'CREATE TABLE ['
+@MyTable+
'] (
[id] [bigint] IDENTITY (1, 1) NOT NULL ,
[word] [nvarchar] (255) COLLATE Latin1_General_CS_AS NULL,
[date] [bigint] NULL ,
CONSTRAINT ['
+'PK_'+@MyTable'+
] PRIMARY KEY CLUSTERED
( [id] )
)'

exec (@sql)

Before exec (@sql) it would be better to do a print @sql and see if there is a mistake.

Greetings
Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

Cornelius19
Starting Member

30 Posts

Posted - 2008-07-16 : 12:41:28

Thanks a lot! I tried it and got the following error message:

Server: Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near '+
] PRIMARY KEY CLUSTERED
( [id] )
)'.

Did it work for you?

It works without the constrait however:

select @sql =
'CREATE TABLE ['
+@MyTable+
'] (
[id] [bigint] IDENTITY (1, 1) NOT NULL ,
[word] [nvarchar] (255) COLLATE Latin1_General_CS_AS NULL,
[date] [bigint] NULL )'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-16 : 13:04:57

Hi,
I have made a mistake and i could not test now.
But the mistake is here:
CONSTRAINT ['
+'PK_'+@MyTable'+
' PRIMARY KEY CLUSTERED
( [id] )
)'

delete the red single quote and add the green single quote

There are 10 types of people in the world: Those who understand binary, and those who don't...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-16 : 13:09:45

This is a bad idea, in most cases. Why are you designing a system like this?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Cornelius19
Starting Member

30 Posts

Posted - 2008-07-16 : 14:17:24

webfred,

Thanks, it works.

tkizer,

I have to do a multi-step processing of a table, so the table name will appear several times in the script. I have other tables I need to process in the same way so I can use this script but need to change the table name in the script several times. I thought it is easier to change it only once.

Cornelius

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-16 : 14:20:35

It just depends on where you are running this process. Is it a one-time thing or will it be run from an application? For one-time things, dynamic sql works great. For applications, not so much.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-17 : 05:02:04

In any case, make sure you read this
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Cornelius19
Starting Member

30 Posts

Posted - 2008-07-17 : 09:40:00

Thanks to both of you. I will be cautious.

Cornelius

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources