Dynamic Table Referencing, SP

Hey,

I'm looking at dynamically creating tables, and referencing tables, using a variable name.

An example:

A 'user' signs up. They enter their username, password, and tableName.

a SP grabs this info and does the following:

adds the usrname, pass, tableName, to a table named Users.

It then creates a table (all table details are pre-entered in a sp), but the table name is @.table (references the tableName).

I know how to send variables to a SP, and I can manipulate them, but I'm having trouble working out how to use them as a tablename reference.

The other situation (which I imagine would use the same syntax) is in a select (update etc as well...) statement.

Create StoredProcedure select_table
@.tableName <datatype?!?>
AS
Select *
From @.tableName
Go

How can I make this work? Is it possible? or do I need to generate the query some other way. If so, how can I do it? lol.

Thanks a lot for your help and any direction you can give me
-AshleighI'd do it using the sp_executesql stored proc.

Build your sql statement in your initial stored proc then build a string/varchar variable with the sql statement you want to run and then run that sql statement that you built using sp_executesql.|||Fair warning... dynamically creating a whole bunch of tables in a database in order to support a whole bunch of users is probably not the best way to handle such a requirement. Generally and broadly speaking, "lots and lotsa tables" is usually not a Desirable Thing.

Views are better.|||Yeah, I'd have to agree with that statement...

Unfortunately you can't always convince the "management" that this is the case. ;)

Not until they have had to maintain it anyways... :)|||Hey,

the example is just a simple example for what I hope to achieve.

Using dynamically created tables is the best way to manage the system I am using.

Thx though,

-Quote
I'd do it using the sp_executesql stored proc.

Build your sql statement in your initial stored proc then build a string/varchar variable with the sql statement you want to run and then run that sql statement that you built using sp_executesql.

Can you give me an example of how to do this? I have no idea (or a resource to learn it from?)

For the example, perhaps jsut for the simple case of:

Create StoredProcedure select_table
@.tableName <datatype?!?>
AS
Select *
From @.tableName
Go

Thanks
-Ashleigh|||an example,... sure...

CREATE PROCEDURE select_table
@.tableName varchar(255)
AS

Declare @.strSQL nvarchar(1000)

Select @.strSQL = 'Select * From ' + @.tableName

exec sp_executesql @.Statement = @.strSQL
GO

then your call to the stored proc is something like...

select_table @.tableName='File_Status'|||Fantastic,

That's not hard at all. I thought it was going to be something really complex and scary.

Thanks a lot for you help. Saved me hours of reading random search pages and textbooks.

-Ashleigh|||No worries, happy to be able to help out.|||"Using dynamically created tables is the best way to manage the system I am using."

Ashleigh, no offense, but never in over ten years of database design have I seen an instance where dynamically creating tables is the best way to manage a system. On the flip-side, I've had to fix or write complex code-arounds for many databases that were implemented this way. The reason you were having difficulty finding out how to do what you plan to do is, basically, because SQL Server and relational databases in general are not designed to do things the way you are planning to do them.

See if you can't add the scalability you need through one additional table or even one additional field in an existing table. It could save you hundreds of lines of code and many hours spent in performance tuning.

blindman|||Once again I'd agree with the above statement...

You have probably missed something in your data model if you need to build a database this way.

The only time I could ever see you doing something like this is if your boss (who really shouldn't be involved in the design process) tells you that this is the way he wants it built.

I assume this post links to your other post regarding the use of stored procedures and the data you have talked about in that post... if so you should be able to do everything you have talked about in the other post without having to create these tables.|||Hey,

ok well, here's what I'm doing.

I'm creating a site which creates 'pools'. These pools are created by a 'manager' using a 'managementKey'.

When the manager registers with his key, I register his information including the name of his 'pool'.

A table is then made using the 'pool' name. This table holds all the user information. MemberID, Password, and their details.

The reason we elected to do it this way was, there will be lots of instances where we will be sorting through the table, displaying results etc. and we are looking at a possible 500K-1 million users. That's a lot of data in one table.

To log in, users enter the 'pool' name, 'memberID' and 'password'. It will then look for the memberID and password in the selected field.

Another table is also created for each 'pool' named

'pool'Results. This holds information about each members settings, results, etc. This table will hold around 5 entries a week from each member of that 'pool'.

On paper, this method is much neater and easier to manage than 4-5 massivetables.

If you can think of a better method for this, I'm all ears. But from what I can see (and I'm happy to admit I'm a novice sql man) this is the best way.

-Ashleigh|||I'd stick with the 4-5 tables to be honest...

1 mil records is nothing if you have a good table structure and some decent indexes to aid your searching etc.

Think about it this way,.. if you decide to change the structure of these "pool" tables how are you going to do that if you have 200+ tables to change?

*shudder*|||hm, ok...

well I'll discuss it with the people upstairs, lol.

-Ashleigh|||Originally posted by Ashleigh
hm, ok...

well I'll discuss it with the people upstairs, lol.

-Ashleigh

I've had a chat with the man (lol).

Basically, what we concluded was. Queries won't be searching through 1 million rows. If I use 4-5 tables only, finding results (linking the tables up) will be querying through around

1million x 20 x 8, so um, 160 million rows. That's for each user, probably twice a week. That's a lot of row searching. So by dividing it all up, we thought it would make it run a lot smoother.

In terms of db updating. The db's will all be based on 3 templates. So can you use a script (in ms sql) to update them? reguardless, I don't see any reason why we'd update. Of course, those are famous last words, LOL...

-Ashleigh|||Its still better to go with 4-5 tables. You'll add one additional field to the tables to indicate the manager "pool", and then index this ID. With an index, the optimizer will be able to quickly locate the entries for a particular manager and then search through just those entries, much as if they were in a separate table.

On the plus side, you won't have to write all your code as dynamic. Dynamic SQL is difficult to debug and less efficient than direct SQL.

If this is important to you and your manager, get a database consultant to review your design. Then, if something doesn't work you have someone else to blame. This is what consultants are for. ;)

blindman|||Hey,

yeah, might be worth considering. I'm starting to feel like I could use a good consulting (lol).

On the subject of indexing tables. I'm not 100% sure what this means. Can you point me to something that will explain it?

Thanks
-Ashleigh|||http://www.sqlteam.com/store.asp|||Like blindman said, the indexes will reduce your processing/quering so you are only dealing with what you need to...

Unfortunately I haven't really seen anything online that describes indexes and how they work etc...

Perhaps another post requesting such might attract the attention of someone who has. ;)|||Did you look in BOL?

Also Ken Hendersons books are almost a must have...|||Originally posted by Brett Kaiser
Did you look in BOL?

Also Ken Hendersons books are almost a must have...

No, I havn't (either of those resources).

I'll look into them though, thanks

-Ashleigh|||Just a fair warning - if you do not know what "Indexing tables" means then you need to do your homework a little better. From what I have read on the database requirements, this design is not very complicated and might require a couple of lookup tables and a details table.

Kaiser gave you some good resources.

Good luck.|||mm. thx rnealejr, indexing tables is something I have on my list of must find out more about before I implement this database. Unfortunately, that list started out very long. Having never done database design before, I just got shafted this job so... lots of 'homework' and no real time to do it, lol. Such is life I guess.

Yea, the database itself isn't very complicated. I was just concerned about it's size. Evidently though, it's size is not a concern as long as I do good indexing.

I have since got a few good resources on indexing, and I believe I have that all under control.

Thanks to everyone for their input on good resources etc.

rnealejr i just have a small question relating to what you wrote. lookup tables and details tables.

What exactly do you mean by this? When I read it, I picture a table with just ID and names, to make searching for the ID num faster. is that what you mean?

Also, I have a very n00b question about indexes that I can't find an answer to. Do I need to change my searching at all? to use them? or is it automatic? ie I'm indexing usrname, so if I say

select *
from member
where name = @.name

that will use the name index on the table?

Also, I read something about making indexes have multi values. IE I have a db with these columns (example)

userID, username, password, groupName

if I make a unique index of username, groupName (ie both in the same index)

do I use that index simply by saying

select *
from member
where username=@.username AND groupName = @.groupName

(note, these are examples, don't worry about why I'm doing these procedures, because I'm not, lol).

-Ashleigh|||My battery is running out - so I have to make this fast - but I believe the answer to most of your questions is yes (or at least you are on the right track). When you create an index - the query optimizer picks the best path to retrieve the data. So if an index exists and the optimizer sees that it is faster with the index versus and sequential scan of the table - it will pick the index. Try it out - open query analyzer and use the "Show Execution Plan".

To answer your question about table design - yes - you want to normalize your data (check out the 1st - 3rd normal forms for reference).|||use of the indexes should happen automagically...

you can view the execution plan for any sql statement in the query analyzer to see what indexes it will use to perform the query.

if you use a composite index (eg. an index that uses more then one field) then the query/database can use part or all of the index to run it's queries (from what I remember - someone please confirm this).

best shown by an example I think eg.

your index has username and group...

if you search just using username it will use that index...

if you search just using group it won't

if you search using both it will.

HTH|||Fantastic,

there is hope for me yet, lol.

thanks a lot rnealejr, and rockslide (yet again, lol).

I almost feel like I know stuff now, woot.

Nah, this is a great resource here. I think I better spend a few hours on a flash and asp.net forum tonight. Pay back my karma debt, lol.

Thanks again.

(still trying to convince them to buy me some of those books Kaiser pointed out, lol).

BOL sounds really good as well. I'm going to put in some time trying to track that down tonight too.

-Ashleigh|||bol should be on the disks you used to install ms sql (it's one of the components).|||Originally posted by rokslide
bol should be on the disks you used to install ms sql (it's one of the components).

lol, ah ok sweet. Yea I use that a bit, but perhaps not as much as I should.

thx again,
lol