How to create CLR stored procedure in visual studio 2010 in vb.net

We can create CLR stored procedure in visual studio 2010 in vb.net very easily if you following steps:

Step 1: Create a Visual Basic SQL CLR Database Project in visual studio.

Step 2: You will get Add Database Reference window. Click on Add New Reference...

Step 3: In New Database Reference window fill database information where you want to create CLR stored procedure.

Step 4: In pop up, you should enable SQL/CLR debugging for this connection.

Step 5: Right-click on your project in solution explorer and choose Add -> New Item...

Now select Stored Procedure and write down any name of your stored procedure.

Step 6: You will get auto gendered code something like this:

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures

    <Microsoft.SqlServer.Server.SqlProcedure()> _

    Public Shared Sub  uspGetEmployeeInfo ()

        ' Add your code here

    End Sub

End Class

Step 7: Here you can write code of your stored procedure according to your requirement. In this example, we are going to create such CLR stored procedure which displays the information of employees from Employee table. Open SQL server management studio (SSMS):

a. Creating Employee table in the SampleDb database.

USE [SampleDB]

CREATE TABLE [dbo].[Employee](

    [EmpID] BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,

    [EmpName] VARCHAR(100) NULL,

    [Age] TINYINT NOT NULL DEFAULT(0),

    [Country] VARCHAR(50) NULL,

 )

b. Insert some data into it. Let us assume Employee table has following data:

EmpID	EmpName	Age	Country
1	Scott	32	USA
2	Greg	31	UK
3	Akram	30	Pakistan
4	Rohit	23	India
5	Nicolus	28	Russia

c. Enable "clr enabled" configuration option.

EXECUTE SP_CONFIGURE 'clr enabled', 1

GO

RECONFIGURE

GO

d. Set your database trustworthy:

ALTER DATABASE SampleDB SET TRUSTWORTHY ON

Step 8: Edit the code of CLR stored procedure in visual studio:

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures

    Private Shared command As SqlCommand

    Private Shared reader As SqlDataReader

    <Microsoft.SqlServer.Server.SqlProcedure()> _

    Public Shared Sub uspGetEmployeeInfo()

        Using connection As New SqlConnection("context connection=true")

            connection.Open()

            command = New SqlCommand("SELECT EmpName,Age,Country FROM Employee", connection)

            reader = command.ExecuteReader()

            SqlContext.Pipe.Send(reader)

        End Using

    End Sub

End Class

Step 9: It is not possible to create CLR stored procedure in .net framework 4.0 so change it to 3.5

For this right click on your project and choose Properties. Now select Compile tab and click on Advanced Compile options... and change the .net framework

Step 10: Now make your project unsafe. For this select Database tab in the properties window of your project and choose Permission level Unsafe.

Step 11: Now build your solution and deployed your project.

Step 12: If deployment is successful then it will create CLR stored procedure in your database in SSMS.

Step 13: We cannot modify or see the script of CLR procedure in SSMS. Here we can only execute this stored procedure

USE [SampleDB]

DECLARE  @return_value INT

EXEC @return_value = [dbo].[uspGetEmployeeInfo]

SELECT   'Return Value' = @return_value

Output:

EmpName	Age	Country
Scott	32	USA
Greg	31	UK
Akram	30	Pakistan
Rohit	23	India
Nicolus	28	Russia

Note: It will also create one assembly inside Programmability -> Assemblies of SSMS

Output parameters of CLR stored procedure in vb.net.
How to transfer or create CLR stored procedure from one database to another database in SQL server.