How to use dot net function into SQL Server

Step 1: Create your function in dot net using C#/VB.net

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.SqlServer; // This NameSpace Important to Add

namespace SumNum

{

    public class SumNum

    {  

        //****** this line is Importanct to write********//

        [Microsoft.SqlServer.Server.SqlFunction()]

        public static int SumNumbers(int a, int b)

        {

            return a + b;

        }

    }

}

  

Step 2: Find Your Application Dll(In My Example the DLL is SumNum.dll)

Copy You Dll file Location

Like in my example, the Dll Location is:  

D:\SumNum\SumNum\bin\Debug\SumNum.dll

 

step 3: Go to Sql Server Management Studio and write given Below queries

-- this query will enable DOT NET CLR function in your SQL SERVER 2005

sp_configure'clr enabled', 1

RECONFIGURE WITH OVERRIDE

-- Add Your DOT NET .Dll Assembly in your SQL SERVER 2005

CREATE ASSEMBLY MyDotNetFunction

FROM 'D:\SumNum\SumNum\bin\Debug\SumNum.dll'

 

-- you may also use Alter Command to change in Assembly function

create FUNCTION [dbo].[MySum](@a [int], @b [int])

RETURNS [int] WITH EXECUTE AS CALLER

AS EXTERNAL NAME [MyDotNetFunction].[SumNum.SumNum].[SumNumbers]

Step 4 : Now You may use this function in your SQL Queries as see given below:

select dbo.MySum(id,Head_id) as MyFunctionValue from emp

OutPut:

Cheers, Thank You