my code stock.com

Determine if a SQL stored procedure is running

Snippet options

Download: Download snippet as is-running-stored-procedure.sql.
Copy snippet: For this you need a free my code stock.com account.
Embed code : You will find the embed code for this snippet at the end of the page, if you want to embed it into a website or a blog!

/****** Object:  StoredProcedure [dbo].[usp_isRunningStoredProcedure]    Script Date: 04/11/2012 19:57:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/* ***************************************************************************************************
 * Project Name:          	THOT
 * Project Version:      	$VersionInfo$
 * Creation Date:         	5 april 2012
 * Author:                	dfe
 * General Description:		$Description$ (don't forget to create MS_Description extended property)
Determine if a SQL stored procedure is running 
 * References:            	http://weblogs.asp.net/johnbilliris/archive/2009/11/02/how-to-determine-if-a-sql-stored-procedure-is-running-part-i.aspx
 * ====================================================================================================
 * MODIFICATIONS
 * ----------------------------------------------------------------------------------------------------
 * Date            	Author        			Description			Project id
 * ----------------------------------------------------------------------------------------------------
 * Please complete after each code modification.
 * <date>          	<author>      			<fix id>			<documentation id>
 * <description of changes>
	5 april 2012	dfe
	fix bug and refactoring
 * ************************************************************************************************* */
CREATE PROCEDURE [dbo].[usp_isRunningStoredProcedure]
	@StoredProcedureName  nvarchar(max),
	@IsRunning bit OUTPUT
AS
BEGIN
	DECLARE @stringBeingSought NVARCHAR(MAX)    -- the string we're looking for. The variable to become a parameter if you wish to extend this sp
	DECLARE @handle SMALLINT    -- the spid of the process
	DECLARE @sql NVARCHAR(MAX)  -- the dynamic SQL
	DECLARE @table TABLE ( EventType nvarchar(30) , [Parameters] int , EventInfo nvarchar(4000) )   -- the table variable holding the result of DBCC INPUTBUFFER execution
	SET @isRunning = 0
	SET @stringBeingSought = @StoredProcedureName

	DECLARE procs CURSOR FOR 
	SELECT 
		session_id 
	FROM sys.dm_exec_requests 
	WHERE status IN ('running', 'suspended', 'pending', 'runnable') 
	AND session_id <> @@SPID
	ORDER BY session_id DESC  -- these are the processes to examine

	OPEN procs
	FETCH NEXT FROM procs INTO @handle
	WHILE @@FETCH_STATUS=0
	BEGIN
		BEGIN TRY           
			DELETE FROM @table

			SET @sql = 'DBCC INPUTBUFFER(' + CAST(@handle AS NVARCHAR) + ')'
	               
			INSERT INTO @table
			EXEC (@sql)

			SELECT @sql = EventInfo FROM @table
		END TRY
		BEGIN CATCH
			SET @sql = ''
		END CATCH
			   
		IF CHARINDEX( @stringBeingSought, @sql, 0 ) > 0
		BEGIN
			SET @isRunning = 1
			BREAK
		END
		FETCH NEXT FROM procs INTO @handle
	END
	CLOSE procs DEALLOCATE procs

	SELECT @IsRunning = ISNULL(@isRunning, 0) 
END

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Determine if a SQL stored procedure is running ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'usp_isRunningStoredProcedure'
GO

Create a free my code stock.com account now.

my code stok.com is a free service, which allows you to save and manage code snippes of any kind and programming language. We provide many advantages for your daily work with code-snippets, also for your teamwork. Give it a try!

Find out more and register now

You can customize the height of iFrame-Codes as needed! You can find more infos in our API Reference for iframe Embeds.