Monthly Archives: November 2010

Temp Tables in Stored Procedures

Quite often in TRANSACT-SQL scripts (as distinct from stored procedures), temporary tables are used to help process the resultset. When using scripts that create temporary tables, you may encounter an error which states that the table already exists (e.g. if you run that script twice in quick succession – as I often do in troubleshooting a stored proc that i’m working on).

The temp table that was created by the CREATE TABLE statement when you first ran the script is still hanging around. Hence the error. You need to include the following statement before you create that temp table, to avoid such an error:

--    IF THE TEMPORARY TABLE ALREADY EXISTS IN MEMORY, DROP IT AND RECREATE.  
IF OBJECT_ID(N'TEMPDB..#WORKPLACESEARCH', N'U') IS NOT NULL 
DROP TABLE #WORKPLACESEARCH;