Code snippets etc

Using a global (##) temporary SQL table to prevent a stored procedure from running more than once at the same time

Sometimes it is necessary to ensure that a stored procedure that you are about to execute is not already running.  This might be necessary if, for example, your stored procedure is writing to a physical table.  In this case, you may need to ensure that only one user is deleting/inserting data into the table at any one time.  The SQL below creates a global temporary table which can then be used to determined whether a process is already running.  To test:

  1. Paste the SQL below into 2 query tabs.
  2. Execute in in first tab - the temp table is created.
  3. Execute in the second tab - an error is thrown.
  4. Close the first query tab.
  5. Execute in second query window - the temp table is created.

What is great about this method is that it is self cleaning - the temp table is automatically dropped when the procedures have completed.  Other methods like writing a flag to control table can come undone if something unexpected happens.



IF OBJECT_ID('tempdb..##MyGlobalTempTable') IS NOT NULL
@Message = (SELECT TOP 1 MESSAGE FROM ##MyGlobalTempTable)
RAISERROR (@Message, 16, 1)
'Sorry, John Smith is running the procedure already' AS MESSAGE INTO ##MyGlobalTempTable
SELECT 'A record was inserted into the global tempory table'

by Richard Grieveson