DB: Handling temp tables

[Newsgroup_User]

Another DB question...

We have a function that grabs a random record from a table using a stored
procedure (MS SQL):

--------------------------------

CREATE PROCEDURE dbo.sp_randomJudge_district
@dist int
AS

-- Create a temporary table with the same structure of
-- the table we want to select a random record from

/* Clean up */
If exists (select * from tempdb.dbo.sysobjects where substring(name,1,22) =
'##TempRandomJudgeTable')
Drop Table ##TempRandomJudgeTable

CREATE TABLE ##TempRandomJudgeTable
(
...
)
...

GO

--------------------------------

On occasion, we get this error:

Error accessing Database.
There is already an object named '##TempRandomJudgeTable' in the database.
Microsoft OLE DB Provider for SQL Server

It appears that sometimes the procedure is called before the temp table is
deleted from the previous call of the function, but the first part of the
procedure is to check for that, and delete it if it's there. Hence, I'm a
bit confused.

=================================================================
Win prizes searching google:
http://www.blingo.com/friends?ref=hM72-GU3FWzMFnTqhv-2GE1FNtA

[Newsgroup_User]

Ok - first, do NOT use sp_ to prefix stored procedures; that's reserved. If
you MUST use a prefix, use usp_ instead.

Now that that's out of the way...
> If exists (select * from tempdb.dbo.sysobjects where substring(name,1,22)
> = '##TempRandomJudgeTable')
> Drop Table ##TempRandomJudgeTable
>
> CREATE TABLE ##TempRandomJudgeTable
> (
> ...
> )

That's a rather odd way of using a temp table. Maybe I missed something in
your explanation, but I think declaring a global temp table (##) here is
unnecessary, especially if you're dropping on every execution. Declaring an
ordinary temp table (#) would have the benefit of dropping itself when it
fell out of scope (that is, when the procedure finishes) as well as each
connection being able to simultaneously have its own copy of the table (SQL
Server will add a mangle to the name like C++ does with overloaded
functions).

Is there some other reason you need a global temp table?

[Newsgroup_User]

> Ok - first, do NOT use sp_ to prefix stored procedures; that's reserved.
> If you MUST use a prefix, use usp_ instead.

Oh!? I've just been using the established protocol that was here before I
got here. I'll need to look into that. ;o)

> Is there some other reason you need a global temp table?

I don't think so. This is actually based on a tutorial I read several years
ago, and I wasn't even aware of the global vs. ordinary temp tables. That's
an easy enough fix!

-Darrel

[Newsgroup_User]

.oO(Lionstone)

>Now that that's out of the way...
>> If exists (select * from tempdb.dbo.sysobjects where substring(name,1,22)
>> = '##TempRandomJudgeTable')
>> Drop Table ##TempRandomJudgeTable
>>
>> CREATE TABLE ##TempRandomJudgeTable
>> (
>> ...
>> )
>
>That's a rather odd way of using a temp table. Maybe I missed something in
>your explanation, but I think declaring a global temp table (##) here is
>unnecessary, [...]

Could this also be the explanation of the problem? When there are two or
more concurrent invokations of the stored procedure, they might run in
parallel threads. Thread A comes first, checks the existance of the temp
table and drops it. Thread B jumps in and does the same check. Back to A
a new temp table is created - and now B has a problem. A race condition.

I'm not familiar with SQL server internals, so I'm just guessing.

Micha

[Newsgroup_User]

"Michael Fesser" <netizen@gmx.de> wrote in message
news:5e7un218jlhjvfaljice4ju9c28d0leqoq@4ax.com...
> Could this also be the explanation of the problem? When there are two or
> more concurrent invokations of the stored procedure, they might run in
> parallel threads. Thread A comes first, checks the existance of the temp
> table and drops it. Thread B jumps in and does the same check. Back to A
> a new temp table is created - and now B has a problem. A race condition.
>
> I'm not familiar with SQL server internals, so I'm just guessing.
>
> Micha

You're absolutely correct. SQL Server provides mutex locks for sections of
code which must run in isolation, but I've found that very few database
developers are aware of how to request one. In the case you've described
above, such a mutex would be desirable.

[Newsgroup_User]

"darrel" <notreal@nowhere.com> wrote in message
news:eln6t7$n48$1@forums.macromedia.com...
>> Ok - first, do NOT use sp_ to prefix stored procedures; that's reserved.
>> If you MUST use a prefix, use usp_ instead.
>
> Oh!? I've just been using the established protocol that was here before I
> got here. I'll need to look into that. ;o)
>

If your established protocal uses tbl, fld, etc, then throw the whole thing
out.
This is not C++, and you're not writing software. If you don't know a table
from a column from a procedure, you shouldn't be writing SQL queries. ;)

All ribbing aside, sp_ is used to denote system (not stored) procedures. If
you call a procedure with sp_ in the name, SQL Server will first check the
master database (regardless of which database you've specified in your
connection or with a USE command), then only check the current database if
the procedure is not found in master.

It's a tick off your performance, and if you happen to have a procedure with
the same name in master...