Hi Mike,

I tried to implement the following code to get your recommendation to work. Since I will have many users hitting the database (stored procedure) at the same time, I thought I would use the random number generator to name the temp table. I guess I'm unsure as to whether there will be a problem creating, using, and dropping a table if many users are calling the same stored procedure at the same time and the table name is hard-coded in hte procedure.

I get an error when running the following code that says the object does not exist:

DECLARE @intTableName decimal
SET @intTableName = ( SELECT (rand()*592082031 + rand()*38021837 + rand()*487933409 + rand()*902343309) )

DECLARE @strTableName varchar(50)
SET @strTableName = '#' + CAST(@intTableName AS varchar(49))

DECLARE @strCommand varchar(200)
SET @strCommand = 'INSERT INTO ' +@strTableName+ ' (CompanyName) VALUES ("testing")'
Execute(@strCommand)
----- ERROR HERE

SET @strCommand = 'SELECT * FROM ' +@strTableName
Execute(@strCommand)
----- ERROR HERE

SET @strCommand = 'DROP TABLE ' +@strTableName
Execute(@strCommand)
----- ERROR HERE


Can I do it this way or is there even a need? Will I have "Table already exists" problems if I use a hardcoded table name?

Thanks...
Mike




>SQL Server 7.0 doesn't have table variables. You'll have to use temp. tables.
>
>Basically, create a temp table that has the same structure as your result set but with an addition column that is an IDENTITY column. Insert your results into the temp table and then select from the temp table
>
>rowid BETWEEN start AND stop
>
>where rowid is the IDENTITY column. I was really surprised at the performance.
>
>-Mike