Is there any way to execute a stored procedure, get it's thread id and pause it in someway...particularly in another stored procedure?
If not, I guess I could manually create a mechanism. This would most likely be used for stored procedures that run for long amounts of time in a while loop where i could just check a flag in a record representing the instance of the stored procedure at the top of the loop and do a WAITFOR DELAY within a nexted loop and have the condition set to end when the flag is marked as 'run' as opposed to 'halt.'
Does the SQL Profiler read information from a table in the master database or something? I am wondering how it knows what stored procedures are running and if I can use that information in this experiment.