SQL Server – Loop Inside Stored Procedure Doesn’t Stop After Query Cancellation

sql serverstored-procedures

I have a few nested stored procedures, which have a while loop at the end. When I stop the query, the loop keeps running, though. Every child sp is independant from another and has a duration of less than 1s. Any ideas why it keeps running or how to properly cancel it?

Best Answer

You could have a look at running requests with either sp_whoisactive or with the below query and kill the session associated with the query / stored procedure.

select r.session_id
    , s.text
from sys.dm_exec_requests r
    cross apply sys.dm_exec_sql_text(sql_handle) AS s

Then you just execute the KILL command, like below, with the appropriate integer value from the above result, session which is associated with your stored procedure / query:

KILL <session_id>

Related Solutions

Sql-server – Execute Stored Procedure that cannot be manually stopped

You could create a stored procedure that invokes another stored procedure asynchronously either by spawning a SQL Server Agent Job or using Service Broker.

It would still be possible for someone with sufficient permissions to stop the procedure from executing though (but not via the "Cancel Executing Query" button as your first procedure would return immediately with execution of the main procedure being carried out on a different spid).

MySQL Stored Procedure – Loop Through Table and Delete Rows

There are two possible solutions:

set @z to null in the very beginning of your loop (prior to SET @sql_text2 = concat('....)

or instead of

SELECT id INTO @z FROM table_name WHERE id >= 7000 ORDER BY id LIMIT 1000,1;

use this:

SET @z = (SELECT id FROM table_name WHERE id >= 7000 ORDER BY id LIMIT 1000,1);

Explanation:

When no row is found, the variable is unchanged from its previous value... so it's not that you have to "set it to null before you use it," it's that you have to reset it to null before you use it again, if you are doing something like SELECT ... INTO that won't reset the value if nothing is found.
see: https://dba.stackexchange.com/a/35207/12923

Best Answer

Related Solutions

Sql-server – Execute Stored Procedure that cannot be manually stopped

MySQL Stored Procedure – Loop Through Table and Delete Rows

Related Question