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:
- Paste the SQL below into 2 query tabs.
- Execute in in first tab - the temp table is created.
- Execute in the second tab - an error is thrown.
- Close the first query tab.
- 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.
DECLARE @Message NVARCHAR(100)
IF OBJECT_ID('tempdb..##MyGlobalTempTable') IS NOT NULL
SELECT @Message = (SELECT TOP 1 MESSAGE FROM ##MyGlobalTempTable)
RAISERROR (@Message, 16, 1)
SELECT 'Sorry, John Smith is running the procedure already' AS MESSAGE INTO ##MyGlobalTempTable
SELECT 'A record was inserted into the global tempory table'