How to detect query cancellation signal inside a SQLCLR function

Question

I have a CLR scalar function that does some calculations, these can take a long time.

When the user cancels the execution of the query, it doesn't seem like the function execution is affected, so the query continues to execute forever.

Is there a way to get a signal inside C# that says query is cancelled and cancel the processing inside the CLR function? Like a CancellationToken or something like that?

Answer 1

You should get a ThreadAbortException, and unless you are using threading or tasks, that will cancel the running code. If you are using threading or tasks, you'd have to catch the exception and cancel them.

I tested with this code

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Diagnostics;
using System.Threading;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static int ClrDelay(int durationMs, bool spin = false)
    {
        var sw = new Stopwatch();
        sw.Start();
        try
        {
            while (sw.ElapsedMilliseconds < durationMs)
            {
                if (spin)
                {
                    Thread.SpinWait(10);
                }
                else
                {
                    Thread.Sleep(10);
                }
            }

                return durationMs;
        }
        catch (Exception ex)
        {
            System.IO.File.WriteAllText("c:\\temp\\out.txt", $"Catch at {DateTime.Now} {ex}");
            throw;
        }
        catch
        {
            System.IO.File.WriteAllText("c:\\temp\\out.txt", "Catch");
            throw;
        }
    }
}

And the output after running

select dbo.clrdelay(10000,0)

and canceling in SSMS is

PS C:\temp> cat .\out.txt
Catch at 9/11/2025 10:29:43 AM System.Threading.ThreadAbortException: Thread was being aborted.
   at Microsoft.Win32.SafeNativeMethods.QueryPerformanceCounter(Int64& value)
   at System.Diagnostics.Stopwatch.GetTimestamp()
   at System.Diagnostics.Stopwatch.GetRawElapsedTicks()
   at System.Diagnostics.Stopwatch.GetElapsedDateTimeTicks()
   at System.Diagnostics.Stopwatch.get_ElapsedMilliseconds()
   at UserDefinedFunctions.ClrDelay(Int32 durationMs, Boolean spin)