Find out if a stored procedure is running

Question

Here is my stored procedure that I run in SQL Server 2012.

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

This stored procedure can be launched from several places in the application. Even a DBA can run a stored procedure using SSMS if necessary.

So far so good.

The problem is that if something goes wrong, or if the database administrator cancels the execution of the stored procedure, the value IsRunningin is RunningProcessesnever updated to 0. Therefore, the system always believes that the stored procedure is started even if it is not there.

I found the following script on the network that checks if the script is working.

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')

script, , ? , RETURN. , .

Answer 1

. SQL Server , , .

ALTER PROCEDURE usp_ProcessCustomers
AS
BEGIN
    BEGIN TRANSACTION

    declare @Lock int
    EXEC @Lock = sp_getapplock @Resource = 'ProcessCustomers',
                               @LockMode = 'Exclusive'
    IF (@Lock < 0)  -- already locked by another process
       RETURN;

-- Do processing here
-- Do processing here

    EXEC sp_releaseapplock @Resource = 'ProcessCustomers'

    COMMIT TRANSACTION
END