pausing stored procedures

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.'You can look at the SP. Find a query that it hasn't executed yet and lock a table that is involved in that query.

You could also dedicate a resource table to the SP, code a select or update of that table before every statement then locking that table will pause the SP.|||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.