Creating SQL Server CLR Function In Visual Studio 2015 – Andrey Zavadskiy

If you try to find an example how to create CLR code for SQL Server, you may encounter MSDN article How to: Create and Run a SQL Server User-Defined Function by using Common Language Run-time Integration, but it describes the process for Visual Studio 2010. Now I’ll show how to do it in VS 2015.

1) Create or open SQL Server Database Project.
2) Add new item and choose SQL CLR C# User Defined Function type.
3) Write the code like in this primitive case.

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

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlDouble ClrFunction(SqlInt32 value)
    {
        double x = value.IsNull ? 0 : (double)value * 2.0;

        return x;
    }
}

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlDouble ClrFunction(SqlInt32 value)

{

double x = value.IsNull ? 0 : (double)value * 2.0;

return x;

}

It’s important to use correct types for SQL Server and C# – SqlDouble and double respectively.
4) Publish the project.

Although the project was deployed successfully, you need to enable CLR on SQL Server. Run the following script:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

sp_configure 'show advanced options', 1;

RECONFIGURE;

sp_configure 'clr enabled', 1;

RECONFIGURE;

If you won’t do that, you’ll get an error when you try to run this CLR function:

Msg 6263, Level 16, State 1, Line 1 Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

Leave a Reply Cancel reply