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