CREATE TABLE with variable name

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)

CREATE TABLE with variable name

Author

Topic

cobus
Starting Member

2 Posts

Posted - 2005-01-19 : 15:04:39

Greetings to you all.
I am trying to create a stored procedure which creates a table.
But i need to create different tables for each customer. The table structure of the table is the same for all the customers, thus i want to pass the stored procedure a parameter which contains a value which is unique to each customer.

something like this:
CREATE TABLE @companyTableName
(
CompanyName varchar(100),
Address varchar(50)
)

But i dont seem to find a way for the CREATE STATEMENT to accept the parameter as a valid name. Can anyone please help me with this..?
Any help will be greatly appreciated!

Regards,

Cobus

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-19 : 15:13:11

Why not use 1 table and add a column that is CustomerId or something and make it part of the predicate when they view their data?

Much easier.

Brett

8-)

cobus
Starting Member

2 Posts

Posted - 2005-01-19 : 15:17:23

Brett,

We currently use it the way you are using it, but there is allot of data from many different tables we need to gather for a service. As the tables grow larger the service and clients are having more trouble using the system, server timeouts etc..!

We are currently looking at ways to improve the database design without radical changes.
Is what i asked in my first post possible to do..?

Regards,

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-19 : 15:49:46

>> We are currently looking at ways to improve the database design without radical changes.

Creating one table per customer instead of having one with customerid is not a radical change.
Just a minor tweak.
....I am having cold sweats and laughing intermittendly.....
aaaaaaaaaaaaaaaaarrrrrrrrrrrrghhhhhhh

Ok, you need to use dynamic sql
declare @tname varchar(50)
set @tname = 'tNumber_no23471'
EXEC('CREATE TABLE ' + @tname + '( blah blah )')

I don't think this "will not improve the design without radical changes" though.

Have you looked at indexes and otherwise tried to improve performance?

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources