Global temporary table and SP

[Yannick Turgeon]

Hello all,

I'm using SS2K on W2K.

Brieffing: Many months ago, I created a stored procedure only used by those
with admin rights in SS. Now, someone else (without admin rights) has to run
it. I gave him rigth to execute the SP but, at the second and more
execution, he got a error message concerning a temp table already existing
(see further).

The SP:
------------------------------------------------------
CREATE PROCEDURE MySP
@Type INT

DECLARE @strSQL AS VARCHAR(4000)

IF EXISTS (SELECT table_name FROM tempdb.information_Schema.tables WHERE
table_name = '##MyTmpTable')
DROP TABLE ##MyTmpTable

SELECT @strSQL = 'SELECT MyField1, MyField2, MyField3 INTO ##MyTmpTable FROM
MyTable'

EXECUTE(@strSQL)

IF @Type = 1
SELECT MyField1
FROM ##MyTmpTable
ELSE IF @Type = 2
SELECT MyField2
FROM ##MyTmpTable
ELSE
SELECT MyField3
FROM ##MyTmpTable
GO
------------------------------------------------------

The error I got on the second time the user run the sp is: "Table
##MyTmpTable already exists." The front-end where this SP is run is A97.
That's where I got this message. This SP looks like a simple SELECT query
from A97 users perspective.

Please, do no argue about the way of doing the work done! It is simplified
at most in order to make it short and easy to read. I have to use the
command "EXECUTE(String)" and, because of this, I connot use a local
temporary table instead of a global one.

I suspect non-admin user cannot drop global temporary table, but the error
message makes me believe that this code line is not even run, as if the
condition "IF EXISTS(...)" return false even if the table actualy exists.

Anybody can help about this? What should I do to solve this problem?

Yannick

[joshsackett]

If two people attempt to execute that procedure at the same time they
will get a conflict because it already exists. You could wrap the whole
thing in an execute statement and change the global temporary table to
a local (using a single #).

Also, try creating the temporary table first and inserting data into it
rather than creating it on the fly. You reduce the amount of locks on
"MyTable".

[Yannick Turgeon]

It solved the problem. Many thanks.

Yannick

Also, try creating the temporary table first and inserting data into it
rather than creating it on the fly. You reduce the amount of locks on
"MyTable".