CLR User Defined Functions - SQL Programmers

The User Defined functions is routines that can take parameters, perform operations and return the result. There are three types of functions available. They are

• CLR Scalar-Valued Functions
• CLR Table – Valued Functions
• CLR User-Defined Aggregates

CLR Scalar Valued Functions

A Scalar valued function will return the single value such as a string, integer and bit value. We can create scalar-valued user-defined functions in managed code using any .Net Framework programming Language.     The input parameters and the type returned from a SVF can be any of the scalar data types supported by SQL Server.

The following example will remove all the special characters from the PhoneNumber Field.

Steps for defining CLR functions

• Open File→New→Project
• Open Database→SQL Server menu from the Installed Templates Section from the left side

• Select the Visual C# SQL CLR Database Project from the New Project Window. And type the project Name as MyFirstCLRFunction
• Click Ok Button now.
• Then the Add Database Reference Window will be displayed

• Select the existing reference or add you New Reference.
• Click the Add New Reference button to add our current database reference.

• Click ok button
• Now your database reference will be added in the Database Reference window.
• select the Reference click Ok.

• Now Out MyFirstCLRFunction Project is created.
• Now right click on the MyFirstCLR Project and select Add→New Item

• Select the User-Defined Function Item from the Add New Item Window. And name it as fnRemoveSplChars.cs then Click Add

• Now the fnRemoveSplChars.cs will be added in your project.
• Double Click on fnRemoveSplChars.cs and open the coding window.
• Write or modify the following code.

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

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fnRemoveSplChars(string Phone)
    {
        Phone = Regex.Replace(Phone, @"\D", "");
         return new SqlString(Phone);
    }
};

• Now Deploy the solution

• You will be getting the build Succeeded message.
• Go to the SQL server now.
• The fnRemoveSplChars() functions is displayed in the ‘Functions’ Section. Refer the following image

• Execute the following code.

SELECT  chvSalesPerson ,
        dbo.fnRemoveSplChars(chvPhone) AS Phone
FROM    dbo.tblTmpDataImport

• The result will be displayed as follows