How to make extended store procedure in C#

Question

I have a C# code in a class lib and i want to make it into working extended store procedure is that even possible? I have followed the tutorial in the ms docs but when i try to execute my procedure in mssql i get this error: Could not find the function xp_sum in the library xp_sum.dll. Reason: 127(The specified procedure could not be found.). I can see that the extended store procedure is registered.

namespace xp_sum
{
    public static class Calculate
    {
        public static int xp_sum(int a, int b)
        {
            return a + b;
        }
    }
}

Answer 1

Typically, you would implement extended stored procedures in native code, that is, the language would be C++. I don't think you can to it managed code, that is .NET and C#.

Then again, extended stored procedures is a very old feature that you have very little reason to use.

You can run C# from SQL Server, but in that case you would implement a CLR stored procedure, which is more straightforward than extended stored procedure - but still a bit cumbersome. See further https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/clr-stored-procedures.

Answer 2

Extended stored procs use the c calling conventions. You would need to create bindings for all the api calls. Also the c# GC would be an issue. And finally extended stored procs are deprecated in favor of clr integration.

you should choose CLR Integration

https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration/common-language-runtime-integration-overview?view=sql-server-ver16