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:
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.