stored procedure

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

  • Double post, continue discussions here

    --Ramesh


  • 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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply