Is it advisable to use a temp table when the stored procedure could be run simultaneously by different users?

Question

I am working on a stored procedure that retrieves the objectGUID from active directory. I am storing the result in a temp table and then returning the value in an output parameter for use with other processes. The SP will be called from different stored procedures as well as web applications PHP, ASP Classic and ASP.Net.

I read HERE that (regarding temp tables):

If created inside a stored procedure they are destroyed upon completion of the stored procedure. Furthermore, the scope of any particular temporary table is the session in which it is created; meaning it is only visible to the current user. Multiple users could create a temp table named #TableX and any queries run simultaneously would not affect one another - they would remain autonomous transactions and the tables would remain autonomous objects. You may notice that my sample temporary table name started with a "#" sign.

Sounds like I am good to go but I wanted to get some advice to make sure there aren't any gotchas I am unaware of. Here is the SP.

Thanks in advance.

CREATE PROCEDURE stp_adlookup
@user varchar(100),
@objectGUID varbinary(256) OUTPUT
AS
SET NOCOUNT ON;
DECLARE @qry char(1000)
CREATE TABLE #tmp(
objectGUID nvarchar(256)
)

SET @qry = 'SELECT *
FROM openquery(ADSI, ''
SELECT  objectGUID              
FROM    ''''LDAP://mydomaincontroller.com''''
WHERE sAMAccountName = ''''' + @user + '''''
'')'
INSERT INTO #tmp
EXEC(@qry)
SELECT @objectGUID=CAST(objectGUID as varbinary(256))  FROM #tmp;
DROP TABLE #tmp
SET NOCOUNT OFF;
GO

Answer 1

Yes, each user will get their own copy of the #temp table, even if they run at the exact same time.

(However, don't use global ##temp tables, signified with two leading pound/hash signs.)

But why do you need a #temp table here at all? Something like this should work (untested, as I don't have LDAP anywhere near me):

CREATE PROCEDURE dbo.stp_adlookup -- ALWAYS use schema prefix
  @user varchar(100),
  @objectGUID varbinary(256) OUTPUT
AS
BEGIN -- use body wrappers
  SET NOCOUNT ON;

  DECLARE @qry nvarchar(max); -- don't use CHAR for dynamic SQL

  SET @qry = N'SELECT @o = objectGUID
    FROM openquery(ADSI, ''SELECT  objectGUID              
      FROM    ''''LDAP://mydomaincontroller.com''''
      WHERE sAMAccountName = ''''' + @user + ''''''')';

  -- can probably parameterize the above, but those single
  -- quotes are a nightmare. Not sure if they're necessary
  -- but I do not feel like trying to untangle them.

  EXEC sys.sp_executesql @qry, N'@o UNIQUEIDENTIFIER', @o = @objectGUID OUTPUT;

  -- SET NOCOUNT OFF; -- don't do this.
END
GO

Answer 2

You should be just fine, we have countless SPs here that get run 1000s of times a day with temp tables that are named the same and don't have any issues.

Here's a visual example. I've created 2 tables on my SQL2014 instance. One was created from SPID 53, the other from SPID 57. Here's what it looks like in Object Explorer:

As you can see, though they are 'named' the same, at the very end, there's a lovely set of characters that make the tables different. The only difference is I executed the CREATE statements from different query windows. This is just a visual way of showing it. When you query the #tmp table, you only query the table that applies to your session.

I will make one suggestion, though. It's something that I'm completely guilty of and I'm working on transitioning to. Use sp_executesql instead of EXEC(). Aaron Bertrand wrote this as one of the 'Bad Habits to Kick':

• https://sqlblog.org/2011/09/17/bad-habits-to-kick-using-exec-instead-of-sp_executesql

Basically, using sp_executesql will reduce the chance of SQL injection and there's a higher chance that the execution plan can get re-used. Aaron goes into much more detail in the article, but that's the 1000-foot-view.

Answer 3

from a broad sense you'll be just fine doing it this way. Stored procedures have limited scope, so even though (example) 3 users execute the same stored procedure and the temp tables will not co-mingle, they won't even see each other.

As long as you don't need to share the results with a different session or a user running a different process Temp table could be a perfectly sound way to go.