How to retrieve @@SPID from running stored procedure called from C# code

Question

I am trying to implement "cancel" functionality on pages with potentially long running select queries or processes. Indexing and other optimizations have been done to minimize this possibility, but during higher server load times some waiting on complex processes is still possible.

When a process has started, a modal busy spinner is displayed with a Cancel button. If the user chooses to stop the process, I want to use the following steps:

I have a test stored procedure:

BEGIN
    DECLARE @SpID varchar(10) = CONVERT(varchar(10), @@SPID);
    RAISERROR(99999, 10, 1, @SpID) WITH NOWAIT;
    WAITFOR DELAY '00:00:15'
END

The idea is to accomplish the following...

1. In the called stored procedure, get the assigned SPID from @@SPID
2. Use the RAISERROR statement to create a non-error condition message containing the SPID
3. While the WAITFOR delay is running, the application function that called the stored procedure retrieves the message with the SPID
4. Send the SPID to another stored procedure to kill that process (KILL will take care of closing the connection and rolling back the data action(s))

My understanding so far is that the message from this setup can be picked up by the calling ASP.NET C# code, but I can't find an example that will retrieve the message while still allowing the calling code to continue (with ExecuteNonQuery, ExecuteReader or ExecuteScalar).

/* CommentTest Procedure */
CREATE PROCEDURE CommentTest
AS
BEGIN
    DECLARE @SpID varchar(10) = CONVERT(varchar(10), @@SPID);
    RAISERROR(99999, 10, 1, @SpID) WITH NOWAIT;
    WAITFOR DELAY '00:00:30'
END

private string _spID;

private string RunCommentTestWaitForSP()
{
   _spID = "";

   try
   {
      using (SqlConnection conn = new SqlConnection(connStr))
      {
         using (SqlCommand cmd = new SqlCommand("CommentTest", conn))
         {
            cmd.CommandType = CommandType.StoredProcedure;

            if (conn.State == ConnectionState.Closed)
            {
               conn.Open();
            }

            // this is where I'm stuck...
            cmd.ExecuteNonQuery();
            _spID = "The message returned from RAISERROR...";
         }
      }
   }
   catch (Exception ex)
   {
      log.Error("RunCommentTestWaitForSP exception: " + ex.ToString() + " / " + ex.Message);
      log.Error("RunCommentTestWaitForSP stack trace: " + ex.StackTrace);
   }

   return spID;
}

private void LinkButtonCancelProcess_Click(object sender, EventArgs e)
{
    RunCommentTestWaitForSP();
    if (_spID.Length > 0 && "IsNumeric") {
        "call function to run sp_KillSpID"
    }
}

CREATE PROCEDURE [dbo].[sp_KillSpID]
    @SpID varchar(5)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @KillStatement varchar(10) = 'KILL ' + @SpID;
    EXEC(@KillStatement);
END

Answer 1

You need to handle the InfoMessage event on the SqlConnection object:

private string RunCommentTestWaitForSP()
{
    try 
    {
        using (SqlConnection conn = new SqlConnection(connStr))
        using (SqlCommand cmd = new SqlCommand("CommentTest", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure; 
            conn.InfoMessage += ReceiveSQLInfo;
            conn.Open();
            cmd.ExecuteNonQuery();
        }
    }
   catch (Exception ex)
   {
      log.Error($"RunCommentTestWaitForSP exception: {ex} / {ex.Message}");
      log.Error($"RunCommentTestWaitForSP stack trace: {ex.StackTrace}");
   }
}

private void ReceiveSQLInfo(object sender, SqlInfoMessageEventArgs e)
{
    //sender will be the "conn" connection above
    var spID = e.Message;
  
    // Here you could open a new connection 
    //   (we expect the existing connection is still busy)
    //    and use it to kill the spID
}

Just keep in mind the ExecuteNonQuery() function won't finish until the stored procedure does, which is not until after the WAIT FOR DELAY code is also finished. Therefore the LinkButtonCancelProcess_Click() method also won't do what you expect, since the if() block is not reached until after the stored procedure is finished anyway.

Additionally, depending on how the C# RunCommentTestWaitForSP() is called, the computer may not be able to process any event messages until it finishes.