Binaryworld - Is stored procedure running ? ... [ SQL Server -> Stored Procedure ]

IF EXISTS (select * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[fnSPisRunning]') AND xtype in (N'FN', N'IF', N'TF'))
DROP function [dbo].[fnSPisRunning]
GO
CREATE function fnSPisRunning (@objid int)
returns bit
/*
Description: Tells you IF a stored PROCEDURE IS currently running.

Inputs:
@objid = the object id OF the stored PROCEDURE you wish to test

Return:
0 = stored PROCEDURE IS NOT running (or does not exist)
1 = stored PROCEDURE IS currently running

Example usage:

IF dbo.fnSPisRunning(object_id('LongSP')) = 0
EXEC LongSP
Else
PRINT 'LongSP IS already running'
*/

  begin
   DECLARE @return bit
   DECLARE @syscacheobjects TABLE (
   cacheobjtype nvarchar(17) NOT null,
   refcounts int NOT NULL
   )
   INSERT INTO @syscacheobjects
   SELECT cacheobjtype, refcounts
   FROM master.dbo.syscacheobjects WITH (nolock)
   WHERE objid = @objid
   AND dbid = db_id()
   IF isnull((select refcounts
   FROM @syscacheobjects
   WHERE cacheobjtype = 'Compiled Plan'),0)
   =
   (select count(*)
   FROM @syscacheobjects)
   SET @return = 0 --Not running
   ELSE
   SET @return = 1 --Running
   RETURN @return
end
GO