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.

DECLARE @Message NVARCHAR(100)

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