Functions

In the previous post we created a procedure for SQL Server using the .NET framework. We chose to use C# in that post. In this post we will create a function in SQL Server that will call our C# dll that we will write using Visual Studio.

The first step is to be sure that we have turned on SQL Server Integration. Please see the previous post in this series.

The steps for writing a function using an assembly are as follows:

• Create the dll using Visual Studio
• Import the dll as an assembly (.dll in Release folder)
• Create a SQL Server Function
• Use the function. (SELECT…)

DoubleIt and TripleIt

We are going to write two of possibly the simplest functions of all; one that takes in an integer and returns an integer of twice the value. We will end up with two scalar-valued functions in SQL Server. We will call them dbo.TimesTwo and dbo.TimesThree.

The .NET code can be called what we want and it doesn’t need to be called TimesTwo and TimesThree. We will name them DoubleIt and TripleIt. These to will be methods inside a class we will call myFunctions, which is inside a project called CLRFunctions. Below is what we end up with in SQL Server.

Below is the C# code in Visual Studio.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;  // not necessary
// here is a comment
public class myFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlInt32 DoubleIt(SqlInt32 myInt)
    {
        return myInt * 2;
    }
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlInt32 TripleIt(SqlInt32 myInt)
    {
        return myInt * 3;
    }
}

Note that you need to be careful with data types. If your C# returns a string, your SQL Server function will return an NVARCHAR(). Here is the code to create a function.

CREATE FUNCTION [dbo].[TimesTwo](@myInt [int])
RETURNS [int] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [CLRfunctions].[myFunctions].[DoubleIt]
GO