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

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

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:

enter image description here

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.


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.