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.
| Author |
Topic |
|
Micah
Starting Member
2 Posts |
 Posted - 2004-11-21 : 14:04:41
|
| Hello All!i am looking for a way to dynamically reference a table from within a stored procedure. i know all about using exec with a string. but this defeats the purpose of a SP becuase you cannot store the execution plan and other issues. i basically have a very complex Query that needs to be executed on a table that is created for each user by another procedure. this table is dynamically created suffixed with their initials to keep the table unique. i need to be able to run a query with the format of this :SELECT * FROM [MainTableName+Suffix](the actuall query is much much more complex this is just the basic idea of what i need)i would like to do this without having to resort to string execution.all ideas are much appreciatedthanks in advance!!! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-21 : 14:14:27
|
| Well, having a dynamically named table will not allow an execution plan to be cached either. In this case the dynamic SQL would be the easiest (not the best) way to do this.A couple of options come to mind:-Use a temp table. These are unique within a database connection. Multiple concurrent connections can use the same temp table name without interfering with each other.-Change the table structure so that one table contains all data, including the name of the user of that data. Something like:CREATE TABLE MainTableName (username varchar(30) not null, col1 int not null)You don't need a suffix, your queries would become:SELECT * FROM MainTableName WHERE username='myUserName'This will allow SQL Server to cache an execution plan and is really the best way to accomplish what you want. |
 |
|
|
Micah
Starting Member
2 Posts |
Posted - 2004-11-21 : 14:27:28
|
Thanks i believe you are correct, this will be the best approach.much appreciated!!quote:
Originally posted by robvolk
Well, having a dynamically named table will not allow an execution plan to be cached either. In this case the dynamic SQL would be the easiest (not the best) way to do this.A couple of options come to mind:-Use a temp table. These are unique within a database connection. Multiple concurrent connections can use the same temp table name without interfering with each other.-Change the table structure so that one table contains all data, including the name of the user of that data. Something like:CREATE TABLE MainTableName (username varchar(30) not null, col1 int not null)You don't need a suffix, your queries would become:SELECT * FROM MainTableName WHERE username='myUserName'This will allow SQL Server to cache an execution plan and is really the best way to accomplish what you want.
|
|
|
|
|
|
|
Dynamic table names