title: Find out if the stored procedure is already running
body: <p>Here is my stored procedure which I am running in SQL Server 2012.</p>
  
  <pre><code>ALTER PROCEDURE usp_ProcessCustomers
  AS
  BEGIN
      IF EXISTS (SELECT 1 FROM RunningProcesses WHERE ProcessId = 1 AND IsRunning = 1)
         RETURN;
  
      UPDATE RunningProcesses 
      SET IsRunning = 1 
      WHERE ProcessId = 1
  
  -- Do processing here
  -- Do processing here
  
  UPDATE RunningProcesses 
      SET IsRunning = 0 
      WHERE ProcessId = 1
  END
  GO
  </code></pre>
  
  <p>This stored procedure can be triggered from several places in the app. Even the DBA can trigger the stored procedure using SSMS if needed.</p>
  
  <p>So far so good.</p>
  
  <p>The issue is that if something goes wrong or if the DBA cancels the execution of the stored procedure then the <code>IsRunning</code> value in <code>RunningProcesses</code> is never updated back to 0. So the system always thinks that the stored procedure is running even when its not.</p>
  
  <p>I found the following script on the web which checks if a script is running.</p>
  
  <pre><code>SELECT 
      r.*, t.text 
  FROM 
      sys.dm_exec_requests r 
  CROSS APPLY
      sys.dm_exec_sql_text(r.sql_handle) t
  WHERE 
      r.status IN (N'Suspended', N'Running', N'Runnable', N'Pending')
  </code></pre>
  
  <p>Would it be a good approach to use the above script to find out whether the stored procedure is already running or not? If its already running then I will exit the stored procedure using the <code>RETURN</code> keyword. If this is not a good idea then what would be the best way to fix this problem.</p>