What to know SQL Server 2008 R2 Express Stored Procedure is currently executing or not before execute

Question

I have a stored procedure GenerateAttendance, execution time will take approx 3-4 min. Meanwhile my program requests the same stored procedure for execute.

I want to restrict my program from executing it if it's currently running. Is there any way, in SQL Server 2008 R2 Express, to find the stored procedure is currently executing or what, before requesting to execute it again.

I tried below option but it does not work for me

SELECT  OBJECT_NAME(@@PROCID) 
select OBJECT_SCHEMA_NAME(@@PROCID)

Answer 1

There are 2 stored procedure that are used for this: sp_getapplock and sp_releaseapplock.

Modify the stored procedure in this way:

ALTER PROCEDURE GenerateAttendance  
AS
BEGIN
    -- Acquire lock
    DECLARE @result int
    EXEC @result = sp_getapplock @Resource = 'GenerateAttendance', @LockMode = 'Exclusive'
    IF @result NOT IN (0,1)
        RAISERROR ('Cannot acquire lock', 16, 1)

    BEGIN TRY
        -- Actual code of your SP here

    END TRY 
    BEGIN CATCH
        -- Release lock
        EXEC @result = sp_releaseapplock @Resource = 'GenerateAttendance'
        IF @result != 0
            PRINT 'Cannot release lock'
    END CATCH
END
GO

Answer 2

Added a simpler solution, as @gbbosmiya requested.

IF EXISTS(
    SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
    FROM
        sys.dm_exec_query_stats AS deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
    WHERE dest.TEXT LIKE '%GenerateAttendance%'    
    ORDER BY deqs.last_execution_time DESC)
BEGIN
    PRINT 'This stored procedure is currently executing'
END
ELSE
BEGIN
-- Execute stored proc
END

GO