Dynamically Generate Table Name in Stored Procedure

Question

Dynamically Generate Table Name in Stored Procedure

charlesd

Default port

Points: 1427
More actions
January 28, 2004 at 12:58 pm

#106870

I have a stored procedure in which I test for the existence of a certain table and then drop it, if it exists. I then recreate the same table.
The problem is that this same table would potentially be dropped and recreated by many users, thereby deleting the result set that a given user had placed in the table.
I need to find a way to dynamically generate the table name by appending the contents of an input parameter to a given string. This would make the table name unique and therefore allow for all users to generate their own result set table. I can build the string and store it in a variable, but I don't see how to use a variable as the table name for the DROP or CREATE table statements. I also don't see how to just use string concatenation in those statements to produce the unique name.
Any ideas of how to accomplish this?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Why not use a temporary table? According to BOL "Local temporary tables are visible only in the current session", meaning when you do

CREATE TABLE #FRANK(

col1, char(1)

...

)

only this session can see that table

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

charlesd Default port Points: 1427 More actions · Answer 2

Frank,

Thanks, but I was initially using a temporary table. The problem is that the users want their result set to be accessible for querying after the session ends. I guess what we have is a semi-permanent table. It could be dropped and recreated later by the same user, but, otherwise, I want the table to remain as is for their later use.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Oh, I see.

Well, before creating a table for each and every user there is right now or might be there in the future, why not create one single table that has a key column which stores some user information like SUSER_SNAME() or something like that, use this as a parameter for your sprocs, so that the user can only see that data where parameter matches column value? That way you avoid dynamic stuff.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

ThomasH SSCertifiable Points: 7437 More actions · Answer 4

charlesd ,

You Wrote "...querying after the session ends..."

what do you mean by "session"? New connection, maybe even days later?

Once you understand the BITs, all the pieces come together

charlesd Default port Points: 1427 More actions · Answer 5

ThomasH,

It might be a different sql session, but it probably wouldn't be over a long time period. It would only be for purposes of further querying the result set. Essentially, I'm creating a report output from the stored procedure that is stored in a table. Any querying would probably be done within a short, 15 minute, time period after running the report. The problem is that another user could run the same stored procedure moments after the first user and wipe out the initial result set by dropping and recreating the table. I need to try and find a way to create a unique name for this table, but one that the user would be aware of. That's why I wanted to take the standard table name and append an input parameter to it in order to make it unique. I just can't figure out a way to use this in the DROP or CREATE table statements.

ThomasH SSCertifiable Points: 7437 More actions · Answer 6

Then I think Frank's idea of having 1 table hold results for multiple report requests is a solution. Otherwise, yout would have to make a unique named table, either permanent or ##UniqueTableName, and keep track on client side. I know, in VB6, regular #TableName persist throughout the connection (SPID) if created by a "1st level" call, as opposed to created with dynamic T-SQL within a call.

When you tried the #Table approach, and "lost" the #Table, did you use dynamic T-SQL in the script your client app sent to SQL? Are you re-connecting the client app between usages of the same semi-persistant data?

Once you understand the BITs, all the pieces come together

David Burrows SSC Guru Points: 65142 More actions · Answer 7

I agree with Frank, use a single table with id, otherwise you will have to use dynamic sql.

Far away is close at hand in the images of elsewhere.
Anon.

MikeMcDonald SSChasing Mays Points: 643 More actions · Answer 8

I agree too ... and also suggest adding a datetime column so that you can purge the data after a suitable period.

Logic I've used is (and it works with multiple users) ...

1. Purge old (expired) data
2. Generate new data with timestamp
3. Generate report