Writting a CLR Stored Proc

Posted Date: 30 Nov 2008	Resource Type: Code Snippets	Category: SQL
Author: Gaurav Arora	Member Level: Diamond
Rating:	Points: 10

Writting a CLR Stored Proc

With the launch of SqlServer2005, we got the ability to write CLR from any .Net language.

Overview:

The following code-snippet tells how to write a simple CLR Stored Proc with the use

of C#.

Step:

1. Start Visual Studio2005 or higher
2. Add new file from File -> New -> File
3. Select Class with C#
4. Mark the name as AStepAheadProc.cs
5. Write the following lines:


/* This Example is a part of different 
 * examples shown in Book: 
 * C#2008 Beginners: A Step Ahead
 * Written by: Gaurav Arora 
 * Reach at : Gaurav Arora */


using System.Data.SqlServer;
using System.Data.SqlTypes;

public class AStepAheadProc
{
    public static void FirstProcProc()
    {
        //Client output buffer

        SqlPipe sqlPipe = SqlContext.GetPipe();

        //send the output

        sqlPipe.Send("This is demo Store Proc Assembly");

    }
}

Explanation :

The sqlContext provides The GetPipe() method returns a SqlPipe that we can use

to send results back to the client.

Steps of Use/Working

1. Compile the above code into assembly


csc /target:library c:\AStepAheadProc.cs /r:"D:\Program Files\Microsoft SQL Server\
MSSQL.1\MSSQL\Binn\sqlaccess.dll"

2. Load the created assembly in SQL with the use of CREATE PROCEDURE


create procedure AStepAheadProc
as external name AStepAheadProc.AStepAheadProc.FirstProcProc

3. Execute the Created stored procedure in Query Analyzer


exec AStepAheadProc

Note:

The above code-snippet is just describe how to create a CLT Stored Proc.

Responses

Author: Fool To Code 01 Dec 2008 Member Level: Gold Points : 0

Excellent job

Author: Gaurav Arora 01 Dec 2008 Member Level: Diamond Points : 1

Hi Deepak,

Thanks a lot for your response I will try to post much more quality stuffs here.

Thanks & regards,
Gaurav Arora

Author: Shuby 27 Dec 2008 Member Level: Bronze Points : 2

Very excellent and admirable job. Do it, keep it up. THese kind of resources will help freshers.

shuby

Author: Gaurav Arora 27 Dec 2008 Member Level: Diamond Points : 1

Hi Shuby,

Thanks a lot for your response, I will try my level best to achieve the same kind of resources in future.

Thanks & regards,
Gaurav Arora

Feedbacks

Popular Tags

What are tags ?

Search Tags

Writting a CLR Stored Proc . SqlServer2005 . CLR .

Post Feedback

This is a strictly moderated forum. Only approved messages will appear in the site. Please use 'Spell Check' in Google toolbar before you submit.
You must Sign In to post a response.

	Next Resource: ROW_NUMBER()
	Previous Resource: Bulk Insert
	Return to Discussion Resource Index
	Post New Resource
	Category: SQL

Post resources and earn money!

Related Resources