Blocking Notification

Declare @BESubj Varchar(500)
Select @BESubj = 'Blocking Detected ON  ' + @@SERVERNAME
Declare @SPID int
Declare @cmd Varchar(150)
--Declare @BESubj Varchar(500)
Select @BESubj = 'Blocking Detected ON  ' + @@SERVERNAME
Select SPID
,HostName=convert (char (15), hostname)
,UserName=convert (char (15), nt_username)
,BlokedBy=convert(char(5) ,blocked)
,Application=convert (char (25), program_name)
--,status=convert (char (8), status)
,DBname= convert ( char (10), db_name(dbid))
,SQL=convert (char (150),cmd)
--,CPU=convert (char(6),cpu)
--,physIO=convert(char (7),physical_io)  
into Transit..Blocked_Rows
from master.dbo.sysprocesses
where status <> 'background'
AND cmd NOT IN ('signal handler','lock monitor','log writer','lazy writer','checkpoint sleep','awaiting command')
And blocked <> 0
OR
SPID IN (Select Blocked 
		from  master..sysprocesses SP)
          AND Blocked = 0
      ORDER BY Blocked,SPID

 
If (Select Count(*) from Transit..Blocked_Rows) <> 0
Begin
	if Not exists (select * from dbo.sysobjects where id = object_id(N'Process_Info') )
	create table Process_Info(
	EventType varchar(150) ,
	Parameters int ,
	EventInfo varchar(150)
			)
	Declare Blocking_SPIDS Cursor Fast_Forward For
	Select SPID FROM Transit..Blocked_Rows

	OPEN Blocking_SPIDS

	FETCH NEXT FROM Blocking_SPIDS INTO @SPID
	WHILE @@FETCH_STATUS = 0
		BEGIN

		SET @cmd =  'DBCC INPUTBUFFER(' + CONVERT(varchar, @SPID) + ')'

	
		INSERT INTO Process_Info
		EXEC(@cmd)
	
		SELECT @cmd = EventInfo
		FROM Process_Info

		Update Transit..blocked_rows
		Set SQL= @cmd 
		Where SPID = @SPID


		FETCH NEXT FROM Blocking_SPIDS INTO @SPID


		END
CLOSE Blocking_SPIDS
DEALLOCATE Blocking_SPIDS

EXEC master..xp_sendmail @recipients = '<Email_List>'
,@query = 'Select * from DB..Blocked_Rows Order By SPID'
,@subject = @BESubj
,@message = 'Blocking has been Detected...Pls see the Attached'
,@attach_results = 'TRUE'
,@width = 250
End
Drop table Transit..Blocked_Rows