Sql Server And Clr Integration

Here is an easy example of how to start with CLR Integration in SQL Server.

http://msdn.microsoft.com/en-us/library/ms131052%28v=sql.100%29.aspx
 
--====================
--==    STEP 1        ==
--====================
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
 
--====================
--==    STEP 2        ==
--====================
-- save following C# code as  helloworld.cs
/*
using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
 
public class HelloWorldProc
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void HelloWorld(out string text)
    {
        SqlContext.Pipe.Send("Hello world!" + Environment.NewLine);
        text = "Hello world!";
    }
}
*/
 
--or following VB code as helloworld.vb
/*
Imports System
Imports System.Data
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.Runtime.InteropServices
 
Public Class HelloWorldProc
    <Microsoft.SqlServer.Server.SqlProcedure> _ 
    Public Shared  Sub HelloWorld(<Out()> ByRef text as String)
        SqlContext.Pipe.Send("Hello world!" & Environment.NewLine)
        text = "Hello world!"
    End Sub
End Class
*/
 
--====================
--==    STEP 3        ==
--====================
--Find .NET version  C:\Windows\Microsoft.NET\Framework\(version)
-- and compile DLL by running
/*
csc /target:library helloworld.cs 
 
or 
 
vbc /target:library helloworld.vb
*/
 
--====================
--==    STEP 4        ==
--====================
-- create assembly
CREATE ASSEMBLY helloworld from 'c:\temp\helloworld.dll' WITH PERMISSION_SET = SAFE
 
--====================
--==    STEP 5        ==
--====================
--create procedure
CREATE PROCEDURE hello
@i nchar(25) OUTPUT
AS
EXTERNAL NAME helloworld.HelloWorldProc.HelloWorld
 
--====================
--==    STEP 6        ==
--====================
--test procedure
DECLARE @J nchar(25)
EXEC hello @J out
PRINT @J
 
--====================
--==    STEP 7        ==
--====================
 --REMOVING
/*
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'hello')
   drop procedure hello
 
Once the procedure has been dropped, you can remove the assembly containing your sample code.
 
IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'helloworld')
   drop assembly helloworld
 
*/
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License