Dynamic SQL and global temp tables

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic SQL and global temp tables

Author

Topic

jeffj
Starting Member

5 Posts

Posted - 2004-07-19 : 13:01:17

I've written a stored procedure that takes a unique table name as a parameter, creates a global temp table using dynamic SQL and the exec() command, performs various selects and lookups against this table, and then drops the table.

The procedure works very well however on our production server we've now had an error kicked back that states the temp table doesn't exist at different points in the procedure.

The only thing I can think could be the problem is that all of my dynmaic sql statements are being processed asynchronously.

The format of my stored proc is as follows:

CREATE PROCEDURE sp_getList
(@TableName varchar(250))
AS

SET NOCOUNT ON

set @TableName = '##'+@TableName
IF EXISTS (
select * from tempdb.dbo.sysobjects
where id = object_id(N'[tempdb].[dbo].['+@TableName+']')
and xtype = 'U')
BEGIN
set @sql = 'drop table ' + @TableName
exec(@sql)
END

set @sql = 'CREATE TABLE '+@TableName+' (
rowID int IDENTITY,
contactID uniqueidentifier)'
exec(@sql)

set @sql = 'INSERT INTO '+@TableName+' (contactID)
SELECT ci.contactID
FROM contactInfo as ci with (NOLOCK)'
exec(@sql)

set @sql = 'SELECT ct.rowID,ci.*
FROM '+@TableName+' as ct
LEFT JOIN contactInfo as ci on ci.contactID = ct.contactID'
exec(@sql)

set @sql = 'drop table ' + @TableName
exec(@sql)

SET NOCOUNT OFF

Any insight you could provide as to why I'm getting the error stating the global temp table doesn't exist would be helpful. Again, the procedure works fine on our development machines but on our production server it has failed twice now in the last 3 days (so the majority of the time it works fine)

Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-19 : 13:06:27

Another user was probably running this stored procedure at the same time it execute for this user. The other user dropped the table like it says to at the end of the sproc, but now this user can't find it. That's the problem with global temporary tables, they're global. You need to make sure only one user is running sp_getList. And BTW, don't use sp_ for stored procedure names as SQL Server looks in the master database first for it, which is a slight performance hit.

Tara

jeffj
Starting Member

5 Posts

Posted - 2004-07-19 : 13:11:23

Thanks for the response.

What I didn't mention is that the table name is a guid generated via my middle tier and passed as the tablename to my stored proc. Therefore I don't think that what you described is the problem.

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-19 : 13:31:37

Why does it have to be global?

What value does the identity value have?

Especially whenb there's no ORDER BY

Why don't you add some error handling to find out where you problem is

If it's SQL 2000 why not use a table variable?

NOLOCK will give you dirty reads....the data may not actually exists in your database...

Why not combine the 2 selects?

Are you just trying to assign a row number? Do that in the front end....

Does the rest of your code look like this?

Brett

8-)

jeffj
Starting Member

5 Posts

Posted - 2004-07-19 : 20:08:13

It seems by simplifying my code for my example it makes my logic seem rather foolish. As I said, my code is of a certain pattern (mainly dynamic sql statement blocks that require access to a global temp table).

I would gladly use a table variable if you could show me an example of how to use a table variable inside a dynamic sql statement executed with either EXEC() or sp_executeSQL

From what I've tried it seems you can't pass a table variable as a parameter to sp_executesql. Of course I could be wrong.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources