CODEWITHSUNDEEP
sqlsql-servert-sqlpivottemp-tables
Roopesh Shenoy
Roopesh Shenoy

Reputation: 3447

How to design usage of Global temp table for a multi-user environment? (or alternatives)

I need to create a temporary table in one of my stored procedures. The data to be inserted into the temp table is derived from a dynamic pivot query - hence I am tied to dynamic sql. So it becomes something like this - 

set query = 'select ....'+ pivotcols + 
            ' into ##temp_table
              from base_table
              pivot (
              max(col1)
              for col2 in 
              (' + pivotcols +' as final'

 exec(query)

Here I cannot use local temp table (#temp_table), since the table created within the dynamic sql won't be available to the rest of the stored procedure. So I end up using a global temp table (##temp_table).

Now the problem is if there is unforeseen exit from the stored proc where the table does not get dropped properly, it can raise exceptions when someone else tries to use the same sp. Also even without exceptions, if two people run the same procedure, there could be problems. Is there any solution to this problem? Any alternatives I can use?

Note: I have to use dynamic sql - the pivot query cannot be made dynamic in any other way, since the columns that will be pivoted are decided at runtime. But I am flexible to how the data actually goes into the temp table.

EDIT: edited 'variable' in the question heading to 'table'

Upvotes: 2

Views: 1643

Answers (3)

Hùng Nguyễn Mạnh
Hùng Nguyễn Mạnh

Reputation: 1

You can use local temp table as follows:
(1) Create local temp table, ex: #tmp1
(2) set query = 'Insert into #tmp1 select ....'+ pivotcols + ' from base_table pivot ( max(col1) for col2 in (' + pivotcols +' as final'

exec(query)
-- Here you can use #tmp1

Upvotes: 0

Pankaj Agarwal
Pankaj Agarwal

Reputation: 11311

You can check condition that if global temporary table is exists using below sql statements.

IF OBJECT_ID('##temp') IS NOT NULL
drop table ##temp

Hope it will help for you

Upvotes: 0

bobs
bobs

Reputation: 22194

You could append a value derived from a GUID to the temp table name. Here's one way that also removes the hyphens.

set query = 'select ....'+ pivotcols + 
            ' into ##temp_table' + REPLACE(CAST(NEWID() as varchar(50)), '-','')

NEWID() will give a different value each time.

Upvotes: 2

Related Questions