Stored Procedure Timeouts (SQL Server)

procedures.sql

CREATE TABLE TestTimeout1 (f1 INT)
GO

INSERT INTO TestTimeout1
VALUES (1)
GO

CREATE PROCEDURE SpTestTimeout1 @p1 INT OUT
AS
BEGIN
    WAITFOR DELAY '00:00:10' -- simulate blocking in database
    SELECT * FROM TestTimeout1
    SET @p1 = 1
END
GO

CREATE PROCEDURE SpTestTimeout2 @p1 INT OUT
AS
BEGIN
    SELECT * FROM TestTimeout1
    DECLARE @temp INTEGER
    SET @temp = 1
    WHILE(@temp < 100000)
        BEGIN
            UPDATE TestTimeout1 SET f1= @temp
            SET @temp = @temp + 1
        END

    SELECT * FROM TestTimeout1
    SET @p1 = 1

END
GO

Timeout.java

// SOURCE: https://techcommunity.microsoft.com/t5/sql-server-support/why-is-my-command-not-timing-out-jdbc/ba-p/317248

import java.sql.*;

public class Timeout {

    public static void method1(CallableStatement c, int timeout) {

        long start = System.currentTimeMillis();
        long stop = 0;

        try {

            c.setQueryTimeout(timeout);

            System.out.println("Method1: Executing Callable statement");
            c.execute();
            System.out.println("Callable statement executed");

            int p1 = c.getInt(1);
            System.out.println("Parameter: " + p1);

        } catch (SQLException e) {
            System.out.println(e);
        }

        stop = System.currentTimeMillis();

        System.out.println("Duration: " + (stop - start) / 1000 + "s.");
    }

    public static void method2(CallableStatement c, int timeout) {

        ResultSet rs = null;

        long start = System.currentTimeMillis();
        long stop = 0;

        boolean timedOut = false;

        try {
            c.setQueryTimeout(timeout);

            System.out.println("Method2: Executing Callable statement");
            boolean isThereAresultSetNext = c.execute();
            System.out.println("Callable statement executed");

            int updateCounts = 0;

            while (true) {
                stop = System.currentTimeMillis();

                if ((stop - start) / 1000 > timeout) {
                    System.out.println("Timeout exceeded in code");
                    timedOut = true;
                    break;
                }

                rs = c.getResultSet();
                int updateCount = c.getUpdateCount();

                // If there are no more results or update counts, we're done
                if (!isThereAresultSetNext && updateCount == -1) {
                    System.out.println("No results and update count is -1");
                    break;
                }

                // Check to see if there is a ResultSet
                if (rs != null) {
                    System.out.println("Resultset obtained");

                    while (rs.next()) {
                        //Rudimentary result processing
                        System.out.println(rs.getString(1));
                        //break;
                    }

                    rs.close();
                } // Otherwise, there will be an update count
                else {
                    updateCounts++;
                    if (1 == updateCounts || updateCounts % 10000 == 0) {
                        System.out.println("Update count = " + c.getUpdateCount() + ", iteration: " + updateCounts);
                    }
                }

                isThereAresultSetNext = c.getMoreResults();
            }

            if (!timedOut) {
                int p1 = c.getInt(1);
                System.out.println("Parameter: " + p1);
            } else {
                c.cancel();
            }
            c.close();
        } catch (SQLException e) {
            System.out.println(e);
        }

        stop = System.currentTimeMillis();
        System.out.println("Duration: " + (stop - start) / 1000 + "s.");
    }

    public static void main(String[] args) throws Exception {

        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

        Connection cn = DriverManager.getConnection("jdbc:sqlserver://server..", "uid", "pwd");

        int timeout = 5;
        String sp1 = "{call SPTestTimeout1(?)}";
        String sp2 = "{call SPTestTimeout2(?)}";

        System.out.println(sp1);
        CallableStatement c = cn.prepareCall(sp1);
        c.registerOutParameter(1, java.sql.Types.INTEGER);
        method1(c, timeout);
        method2(c, timeout);

        System.out.println(sp2);
        c = cn.prepareCall(sp2);
        c.registerOutParameter(1, java.sql.Types.INTEGER);
        method1(c, timeout);
        method2(c, timeout);

        cn.close();
        
        // Output
        //    {call SPTestTimeout1(?)}
        //    Method1: Executing Callable statement
        //    com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.
        //    Duration: 5s.
        //    Method2: Executing Callable statement
        //    com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.
        //    Duration: 5s.
        //    {call SPTestTimeout2(?)}
        //    Method1: Executing Callable statement
        //    Callable statement executed
        //    Parameter: 1
        //    Duration: 21s.
        //    Method2: Executing Callable statement
        //    Callable statement executed
        //    Resultset obtained
        //    99999
        //    Update count = 1, iteration: 1
        //    Update count = 1, iteration: 10000
        //    Update count = 1, iteration: 20000
        //    Update count = 1, iteration: 30000
        //    Timeout exceeded in code
        //    Duration: 6s.
    }

}