i'm looking way identify if specific instance of stored procedure running.
for instance, let's have procedure, foo, known run lengthy period of time. start foo part of process , little bit later start separate instance of foo part of process b. (the process external thing kicking off stored procedure.)
later on, want able see if foo running, want see if instance of foo kicked off under process still running.
i've seen number of solutions checking if instance of sproc running, haven't seen regarding specific instance.
here's kindof hoping in magic fakey code:
create procedure foo begin insert fooworklog (handle) values (@@magicvaluetellingmethespecifichandleofthisinstanceofthesproc) -- other stuff end -- 1 process exec foo --let's know has handle 123 -- meanwhile process exec foo --let's know has handle 789 -- little bit later, different process declare @thehandleimlookingfor int = 123 select activehandle sqlserverslistofactivehandles activehandle = @thehandleimlookingfor any ideas?
more info
the following post similar i'm asking check if stored procedure running
however, unless i'm mistaken, solution tells sproc running, not tell me if instance of sproc running instance executed in specific context.
using solution let's if started sproc 1 context (a), started sproc again different context (b). period of time later let's 1 of 2 instances remained running. if follow example other post, tell me sproc indeed running, not tell me if sproc context or sproc context b.
i may mistaken, thought each time sql server executed sproc gave each instance handle id of sort. wondering if there way of identifying handle sproc running under , storing handle in table somewhere reference.
if i'm not being clear yet, can try again example.
i don't know hot procedure handle, workaround write own control.
if have table store process information, lets "tb_procedurecontrol" can insert record when procedure starts , set end date when procedure finishes.
for example:
create procedure myproc @param1... ,@param2... ,@instanceinfo = null --optional parameter begin --create record instance insert tb_procedurecontrol (instanceinfo, start, end) values (@instanceinfo, getdate(), null) <task do> --set end of process update tb_procedurecontrol set end = getdate() instanceinfo = @instanceinfo end 