Implementing Automatic Procedure Termination in SQL Server

Understanding the Problem and the Solution

When working with stored procedures in SQL Server, it’s common to encounter situations where a procedure is stuck or taking longer than expected. In such cases, it’s essential to know how to stop the procedure automatically after a certain period of time.

In this article, we’ll explore one way to achieve this using SQL Server’s built-in features. We’ll delve into the details of how to use lock_timeout and try-catch blocks to implement automatic procedure termination.

Lock Timeout

The first step in understanding how to stop a procedure automatically is to grasp what lock_timeout does in SQL Server.

-- Create a stored procedure
CREATE PROCEDURE myProcedure
AS
BEGIN
    -- Use the lock timeout feature
    SET LOCK_TIMEOUT 1000;
    -- Simulate some work
    WAITFOR DELAY '00:01:00';
END;

In this example, LOCK_TIMEOUT is set to 1 minute (600 seconds). If a procedure is using SET LOCK_TIMEOUT, it will be automatically terminated if it doesn’t release the lock within the specified time limit.

Try-Catch Blocks

Now that we’ve covered lock_timeout, let’s dive into how we can use try-catch blocks to stop a procedure after a few seconds.

-- Create a stored procedure with a try-catch block
CREATE PROCEDURE myProcedure
AS
BEGIN
    -- Declare variables
    DECLARE @error INT;
    DECLARE @message NVARCHAR(4000);

    -- Use the try-catch block
    BEGIN TRY
        -- Simulate some work
        WAITFOR DELAY '00:01:00';
    END TRY
    BEGIN CATCH
        -- Get the error message
        SET @error = ERROR_NUMBER();
        SET @message = ERROR_MESSAGE();

        -- Stop the procedure if an error occurred
        IF @error IN (515, 517, 523) THEN
            RAISERROR (@message, 16, 1);
        END CATCH;
END;

In this example, a try-catch block is used to simulate some work. If the simulation takes longer than 1 minute (60 seconds), an error will be raised and the procedure will be terminated.

Implementing Automatic Procedure Termination

Now that we’ve covered how to use lock_timeout and try-catch blocks, let’s put it all together to implement automatic procedure termination.

-- Create a stored procedure with lock timeout and try-catch block
CREATE PROCEDURE myProcedure
AS
BEGIN
    -- Declare variables
    DECLARE @id INT;
    DECLARE @error INT;
    DECLARE @message NVARCHAR(4000);

    -- Use the lock timeout feature
    SET LOCK_TIMEOUT 1000;

    -- Start a transaction
    BEGIN TRANSACTION;

    -- Simulate some work in proc_2
    WAITFOR DELAY '00:01:00';
    IF @@TRANCOUNT > 0 THEN
        -- If still inside the procedure, roll back and stop it
        ROLLBACK TRANSACTION;
        RAISERROR ('Procedure took too long', 16, 1);
    END IF;

    -- Commit the transaction if we're still inside the procedure
    IF @@TRANCOUNT > 0 THEN
        COMMIT TRANSACTION;
    END IF;

    -- Simulate some work in proc_3
    WAITFOR DELAY '00:01:00';
END;

In this example, a stored procedure myProcedure is created. It uses the lock_timeout feature to stop itself if it takes longer than 1 minute (60 seconds). If it’s still inside after that time limit, the transaction is rolled back and an error message is raised.

Conclusion

In this article, we’ve explored how to use SQL Server’s built-in features to implement automatic procedure termination. We’ve covered lock_timeout and try-catch blocks, and put them together to stop a procedure after a few seconds.

By understanding how to use these features, you’ll be able to write stored procedures that are more efficient and effective.

Recommendations

Use lock_timeout when working with stored procedures to prevent them from taking too long.
Implement try-catch blocks in your stored procedures to catch any errors or exceptions.
Test your procedures thoroughly to ensure they’re working as expected.

Example Use Cases

-- Create two stored procedures, proc_1 and proc_2
CREATE PROCEDURE proc_1 AS
BEGIN
    -- Declare variables
    DECLARE @id INT;

    -- Start a transaction
    BEGIN TRANSACTION;

    -- Simulate some work in proc_2
    WAITFOR DELAY '00:01:00';
    IF @@TRANCOUNT > 0 THEN
        -- If still inside the procedure, roll back and stop it
        ROLLBACK TRANSACTION;
        RAISERROR ('Procedure took too long', 16, 1);
    END IF;

    -- Commit the transaction if we're still inside the procedure
    IF @@TRANCOUNT > 0 THEN
        COMMIT TRANSACTION;
    END IF;

    -- Simulate some work in proc_3
    WAITFOR DELAY '00:01:00';
END;

CREATE PROCEDURE proc_2 AS
BEGIN
    -- Declare variables
    DECLARE @id INT;

    -- Start a transaction
    BEGIN TRANSACTION;

    -- Use the lock timeout feature
    SET LOCK_TIMEOUT 1000;

    -- Simulate some work
    WAITFOR DELAY '00:01:00';

    -- Commit the transaction if we're still inside
    IF @@TRANCOUNT > 0 THEN
        COMMIT TRANSACTION;
    END IF;
END;

-- Call proc_1 and simulate some work
EXECUTE proc_1;

In this example, two stored procedures proc_1 and proc_2 are created. They use the lock_timeout feature to stop themselves if they take longer than 1 minute (60 seconds). The procedures also implement try-catch blocks to catch any errors or exceptions.

When calling proc_1, some work is simulated using WAITFOR DELAY. If the simulation takes longer than 1 minute, an error message is raised and the procedure is terminated.

Last modified on 2025-03-19