stored procedure

• harriet.louis

  Ten Centuries

  Points: 1225

  More actions

  April 30, 2009 at 3:02 am

  #129750

  how to check if a stored procedure is currently running - can anyone plz help with a solution?

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  April 30, 2009 at 4:07 am

  #986529

  Double post, continue discussions here

  --Ramesh
  

  ---

• arun.sas

  SSChampion

  Points: 11831

  More actions

  April 30, 2009 at 4:39 am

  #986544

  Hi,

  try this sp

  create procedure Spinrun (@Parameter varchar(90))

  as

  begin

  create table #temp

  (

  eventtype nvarchar(30),

  Parameters int,

  EventInfo nvarchar(255)

  )

  declare @SPID varchar(10)

  DECLARE OPENTRANS CURSOR FOR

  SELECT SPID from master.dbo.sysprocesses (nolock)

  OPEN OPENTRANS

  FETCH NEXT FROM OPENTRANS into @SPID

  WHILE @@FETCH_STATUS = 0

  BEGIN

  declare @sql nvarchar(100)

  set @sql = N'dbcc inputbuffer ('+@SPID+')'

  insert into #temp exec (@SQL)

  FETCH NEXT FROM OPENTRANS into @SPID

  END

  CLOSE OPENTRANS

  DEALLOCATE OPENTRANS

  declare @Parameter varchar(100)

  select @Parameter = ('%'+@Parameter+'%')

  if exists (select 1 from #temp where eventinfo like @Parameter)

  print 'SP is Running'

  else

  print 'SP is not Running'

  end

  -----

  exec Spinrun 'SP NAME'

  -----

  ARUN SAS