February 22, 2006 at 6:18 am
I have a generic sp that runs for all of out clients that bcps a file for each client. I have to use a global temp table because a local temp table loses scope with bcp. The problem is, obviously some of the batch procs fail because the global temp table still exists from the previous run of the proc. I want to be able to create a dynamic global temp table name. I am stuck with BCP and xp command shell at this time (company policy) I have found away around this but I would like a cleaner way.
...
SET @tmpTableName = '##' + @<variable passed into proc>
exec('Create Table ' + @<variable passed into proc> +
' (Column1
February 27, 2006 at 8:00 am
This was removed by the editor as SPAM
February 27, 2006 at 8:09 am
You have to delete the global temp table. It is different from regular temp table that it will be deleted when you close your session. The global temp table will remain in tempdb until you manually delete it.
February 27, 2006 at 8:12 am
Here is the code to delete the global temp table dynamically.
CREATE
TABLE #TempDiploma (RecordCount INT DEFAULT 0)
SET
@SQL = 'SELECT COUNT(*) FROM tempdb..sysobjects where name = ''##' + @TempTableName + ''''
INSERT
INTO #TempDiploma EXEC (@SQL)
IF
(SELECT RecordCount FROM #TempDiploma) > 0
BEGIN
SET @SQL = ''
SET @SQL = 'DROP TABLE ##' + @TempTableName
SELECT @SQL
EXEC (@SQL)
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply