Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Sep 2003
    Posts
    65

    Unanswered: 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
    -Ashleigh
    -Ashleigh

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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.

  3. #3
    Join Date
    Oct 2003
    Posts
    706

    Exclamation

    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.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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...

  5. #5
    Join Date
    Sep 2003
    Posts
    65
    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
    -Ashleigh

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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'

  7. #7
    Join Date
    Sep 2003
    Posts
    65
    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
    -Ashleigh

  8. #8
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    No worries, happy to be able to help out.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "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

  10. #10
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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.

  11. #11
    Join Date
    Sep 2003
    Posts
    65
    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
    -Ashleigh

  12. #12
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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*

  13. #13
    Join Date
    Sep 2003
    Posts
    65
    hm, ok...

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

    -Ashleigh
    -Ashleigh

  14. #14
    Join Date
    Sep 2003
    Posts
    65
    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
    -Ashleigh

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •