How do check if stored procedure is already running?

[Will192]

I have stored procedure that I don't want 2 copies of it running at the same time. How do I check if the stored procedure is already running in another instance?

I know that I could setup a table and just put a row in the table when it starts and empty it when I leave.

I want to know if there is a check that I can do on some system table that will tell me the same info.

Thanks in advance for any responses to this post.

 

[hmckillop]

This isnt the exact answer of what you were looking for, but its better than creating a table to insert a row etc.
You basically use sp_getapplock and sp_releaseapplock

If you put in this in the start and end of your proc you will in effect ensure only have one instance
of this proc running.

i.e.

CREATE Proc MyProcNew
AS
BEGIN
BEGIN TRAN
	DECLARE @result int
	SELECT 'Getting App Lock ' + convert(varchar, getdate(), 9)
	EXEC @result = sp_getapplock @Resource = 'MyLockName', 
	            @LockMode = 'Exclusive'--,
				--@LockOwner = 'Session'
	
	SELECT 'Got App Lock ' + convert(varchar, getdate(), 9)
	
	
	waitfor DELAY '00:00:10'
	SELECT 'Started Wait ' + convert(varchar, getdate(), 9)
		 EXEC @result = sp_releaseapplock @Resource = 'MyLockName'

	SELECT 'Released App Lock ' + convert(varchar, getdate(), 9)
	
COMMIT TRAN  
END

"I'm living so far beyond my income that we may almost be said to be living apart