SQL Server - Using CLR Types and Functions

In this article, I am going to demonstrate how to use CLR (Common Language Runtime) data types and functions in SQL Server.

Prepare the CLR Type and Function

Open Visual Studio and create a Visual C# Class Library Project named “SqlServerCLR”.

https://api.kontext.tech/resource/e2ff3406-f58f-5d76-a34e-987f3430800e

Create a struct MyCLRType using code below:

using Microsoft.SqlServer.Server;
using System;
using System.Data.SqlTypes;
 
namespace SqlServerCLR
{
    [Serializable()]
    [SqlUserDefinedType(Format.Native)]
    public struct MyCLRType : INullable
    {
 
        private int m_Value;
        public int Value
        {
            get { return m_Value; }
            set { m_Value = value; }
        }
 
        private bool m_IsNull;
 
        public static MyCLRType Null
        {
            get
            {
                MyCLRType type = new MyCLRType();
                type.m_IsNull = true;
                return type;
            }
        }
 
        [SqlFunction]
        public static MyCLRType Parse(SqlString s)
        {
            if (s.IsNull)
            {
                return Null;
            }
 
            // Parse input string here to separate out coordinates 
            int v = s.ToSqlInt32().Value;
 
            MyCLRType type = new MyCLRType();
            type.Value = v;
            return (type);
        }

        public override string ToString()
        {
            if (IsNull)
                return "Null";
            else
                return Value.ToString();
        }
 
        [SqlFunction]
        public static SqlString ToSqlString(MyCLRType myType)
        {
            return new SqlString(myType.ToString());
        }
 
        public bool IsNull
        {
            get { return m_IsNull; }
        }
    }
}

Build the project.

Create user defined type

CREATE ASSEMBLY SqlServerCLR
FROM 'F:\My Projects\SqlServerProjects\SqlServerCLR\bin\Release\SqlServerCLR.dll';
GO
CREATE TYPE MyCLRType 
EXTERNAL NAME SqlServerCLR.[SqlServerCLR.MyCLRType];
GO

If CLR is not enabled, execute the code below to enable it:

exec sp_configure 'clr enabled', 0;
reconfigure;

Create scalar function

CREATE FUNCTION dbo.ParseToMyType
(@string nvarchar(max))
RETURNS MyCLRType
AS
EXTERNAL NAME SqlServerCLR.[SqlServerCLR.MyCLRType].Parse
GO
 
CREATE FUNCTION dbo.ParseToNVarchar
(@myType MyCLRType)
RETURNS NVARCHAR(max)
AS
EXTERNAL NAME SqlServerCLR.[SqlServerCLR.MyCLRType].ToSqlString
GO

Use the defined type

DECLARE @myType MyCLRType= dbo.ParseToMyType('1110');
print [dbo].[ParseToNVarchar](@myType);

Result:

1110