ramlicious Blogs by Tina & Prabhu

November 17, 2010

Creating Managed Stored Procedure in SQL Server using Dotnet Assemblies

Filed under: SQL Server,Visual Studio — Prabhuram @ 9:44 am

Will do it in four steps

Step 1: Create mySample.cs file

using System;
public class mySample
{
 public static int Difference(string a, string b)
 {
  return 100;
 }
} 

Step 2: Compile mySample.cs file to generate a library

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\csc.exe /out:mySample.dll /target:library mySample.cs

Step 3: Create Assembly in SQL server and an external function

EXEC sp_configure 'clr enabled', 1
RECONFIGURE

CREATE ASSEMBLY [myFunctions]
AUTHORIZATION [dbo]
FROM 'E:\Prabhu\dotnet\mySample.dll'
WITH PERMISSION_SET = SAFE
go

CREATE FUNCTION Diff(@firstword NVARCHAR(255),@secondword NVARCHAR(255))
RETURNS int EXTERNAL NAME myFunctions.mySample.Difference
GO 

Step 4: Simply use it

SELECT dbo.diff('a', 'b')

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment

You must be logged in to post a comment.

Powered by WordPress