How can I query the execution status of a stored procedure or a SQL Server Agent job? I'm creating an Active Server Pages (ASP) page that executes a long stored procedure. If a user refreshes the page, the procedure starts and executes again. I want to include a query on the ASP page that determines whether the stored procedure is already running, and if so, prevents re-execution of the stored procedure.
The most reliable way of viewing execution status is to wrap your stored procedure call with some state change-management code. We don't have enough space in this column to drill into detail, but the following description should help. First, create a three-column state table for your application, including Userid, Session Number (assuming that the userid could have multiple active sessions open at any one time), and State. Second, every time you're ready to respond to a user request, read the state table to check that the request is appropriate. For example, if the user is attempting to change the state to X and the state is already X, you can handle the repeated request in your application. And finally, when your stored procedure returns with its final result, you can reset the state to allow the next request.
You can insert the state-change logic at the top and bottom of your stored procedure. If you choose to add this logic and you're running within a transaction, code the state-status check to use a NOLOCK locking hint to avoid blocking.