SELECT results of EXEC statement into variables without temp tables
-
Any ideas on how I can return the results of an EXEC [sproc] into variables without using a temp table?
For example, I'm trying to determine if a path is a directory. Here's code that works:
DECLARE BackupDir nvarchar(200)
IF OBJECT_ID('TempDB.dbo.#FileExists') IS NOT NULL
DROP TABLE [dbo].[#FileExists]
CREATE TABLE #FileExists (FileExists BIT, FileIsADirectory BIT, ParentDirectoryExists BIT)
INSERT INTO #FileExists EXEC master..xp_fileexist @BackupDir
IF (SELECT FileIsADirectory FROM #FileExists) = 0
do something...
However, I want to not use the temp table because this procedure could go screwy if it was run more than once at the same time (droping the temp table while another one is using it). And I know you can't use a table datatype variable to hold the results.
So is there any way to get the results of the xp_fileexist into variables without using a temp table? Thanks for the help!
-
#TempTables are destroyed once a stored procedure completes. So, why are you checking for Existence ahead of time? Does this table get created somewhere else?
Also, if I am not mistaken, SQL Server actually gives #TempTables its own name. Hence, if two different SPID's create the same #TempTable name, there should be no contention. Hopefully, someone who knows better than me will correct or confirm this...
I wasn't born stupid - I had to study.
-
I was checking for existence as a failsafe. When you run the code multiple times in the same session, it would error out if the temp table was not dropped.
So I suppose it would be true that if I were to run it in a stored procedure, the temp table will be dropped each time the procedure exits, regardless of where or when it exits (such as for an error). Then, the temp table should be unique at each run, even if is run multiple times in the same session.
Correct? If so, then I can go ahead and use the temp table. Thanks!
-
Actually the temp objects are destroyed when the connection is closed (destroyed ??), but they are unique to each connection. So 1 or 10 users can create the same temp table (assuming no lock is blocking 'em on sysobjects). It also means that you can create a temp table in a sp, then call another sp from there and use the temp table in the 2nd proc.
It's also considered best pratice to destroy the temp table as soon as you're done (release locks, memory, cleaner code).
-
Thanks for the clarification. However, that then means I do, in fact, need to worry about the temp table being dropped.
Let me clarify a little more about my concern. I'm creating a set of stored procs (which I'll later present for peer review) to handle backups. One stored proc uses a control table to determine which backups to run. This proc will be a job scheduled to run every minute. The proc then will execute another proc, passing the backup type, which will actually perform the backup. This second proc is the one which selects from the temp table. On each run of the main sp, each backup slated for execution is run sequentially, so the temp tables are not a problem. However, I'm concerned about the sp being run once again while the other is still running. For example:
EXEC spDBA_RunBackups
GO
EXEC spDBA_RunBackups
GO
Since I'm going to be scheduling it as a job, the shouldn't happen. But as a developer/dba, I want to ensure that the code will always be a success and is encapsulated. That's why I want to avoid using the temp table. But I suppose the chances of concurrent execution is negligible.
Any thoughts?
-
Well you could always do something like this :
create temp table
check if file exists
set a fiew variables from the temp table
drop the temp table
exec backupProc param1, param2
Or you could create a permanent table,
insert into tableFile (field1, field2) exec xpFileExists
set variables
delete inserted/useless lines from tableFile
exec backupProc
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply