CODEWITHSUNDEEP
sqlsql-server
BanksySan
BanksySan

Reputation: 28500

SQL Temp Table scoped only to the Proc

I need to create a table, with many indexes that is scoped only to the running sproc.

I tried a table variable, but this doesn't seem to support indexes. A local temp table seems to create a 'real' table, and need to be explicitly dropped at the end of the proc, from which I'm inferring that it's also shared across concurrent runs and so would break.

What can I use to store data with indexes that is scoped only to the indicidual instance of the running sproc?

Upvotes: 3

Views: 51

Answers (2)

Monah
Monah

Reputation: 6784

Updated

The answer is don't worry at all since the temp table will be as if it was a local variable inside the stored procedure.

I wanted to make sure if the doubt I had was correct or not, so I made this test

create procedure TestTempData
as
begin
    declare @date datetime = getdate()
    if object_id('#testing') is not null
        drop table #testing
    create table #testing(
        Id int identity(1,1),
        [Date] datetime
    )

    print 'run at ' + format(@date,'HH:mm:ss')

    insert into #testing([Date]) values
    (dateadd(second,10,getdate())),
    (dateadd(second,20,getdate())),
    (dateadd(second,30,getdate()))
    waitfor delay '00:00:15'
    select * from #testing
end

then I ran this query

exec TestTempData

waitfor delay '00:00:02'

exec TestTempData

the result came as

run at 14:57:39
Id  Date
1   2016-09-21 14:57:49.117
2   2016-09-21 14:57:59.117
3   2016-09-21 14:58:09.117

the second result

run at 14:57:56
Id  Date
1   2016-09-21 14:58:06.113
2   2016-09-21 14:58:16.113
3   2016-09-21 14:58:26.113

If the concurrent runs will effect the #temp table, both results should be the same which was not the case, It seems that the temp table inside stored procedure acts like a local variable inside a method.

Before chatting with Gordon Linoff

Since you mentioned that the temp table is shared across concurrent runs, your temp table should be unique for the current run.

Your stored procedure should look like this

create procedure YourProc(@userId int)
as
begin
   if object_id('#temp' + @userId) IS NOT NULL
       execute( 'DROP TABLE #temp' + @userId +'')
    ...
    execute('insert into #temp' + @userId + 'values(...')
end

The above solution will ensure that no conflict will occur and no data will be lost since each execution will be unique per userId

you don't need to drop the table when you finish because it will be dropped automatically by it self

Hope this will help you

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269533

You don't need to worry about dropping the table. SQL Server does that automatically. As explained in the documentation:

  • A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.

This is a result of the scoping rules for access to the temporary table.

I will admit, that in practice, I tend to explicitly drop temporary tables in stored procedures. The differences among:

  • create table temp
  • create table #temp
  • create table ##temp

are all too similar to rely on the fact that the second is dropped automatically, but the first and third are not. However, this is my "problem" and not a best practice.

Upvotes: 3

Related Questions