Click here to Skip to main content
13,598,183 members
Click here to Skip to main content
Add your own
alternative version


28 bookmarked
Posted 15 Oct 2006

How to create CLR store procedure

, 15 Oct 2006
Rate this:
Please Sign up or sign in to vote.
This article will drive you, how to create CLR store procedure


Hi All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p>


Here is some cool stuff related to CLR Store Procedure, I will cover here how to create simple CLR store procedure using VS 2005.<o:p>

I suggest before reading this article please read my previous article named “CLR store procedure vs. T-SQL store procedure” Click here.<o:p>

Please perform following steps sequentially, to create CLR store procedure.<o:p>

1.      Open VS 2005 and click on “New Project”, then select project type of “Database” as shown below.

<o:p>Sample screenshot

<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"><v:f eqn="sum @0 1 0"><v:f eqn="sum 0 0 @1"><v:f eqn="prod @2 1 2"><v:f eqn="prod @3 21600 pixelWidth"><v:f eqn="prod @3 21600 pixelHeight"><v:f eqn="sum @0 0 1"><v:f eqn="prod @6 1 2"><v:f eqn="prod @7 21600 pixelWidth"><v:f eqn="sum @8 21600 0"><v:f eqn="prod @7 21600 pixelHeight"><v:f eqn="sum @10 21600 0"><v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"><o:lock aspectratio="t" v:ext="edit">  <o:p>

2.      Select location for your project and say “OK”. If any connection is been created previously following screen will appear to select available database connection.

<o:p>Sample screenshot

3.      You can add another connection by clicking “Add New Reference…” following screen will appear on click of button. The same screen will appear if you do not have any connection created previously. <o:p>

<o:p>Sample screenshot


4.      Type machine name or IP address of your SQL Server. Select type of authentication you required (here I am using windows authentication), then select database name where you would like to deploy CLR store procedure.<o:p>

5.      Click on “Test Connection” if you got message “Test Connection Succeeded” then click on “OK”, else make sure you have selected proper SQL Server, database and you hold account on SQL Server for defined database.<o:p>

6.      On click of “OK” you will see message window shown below.

<o:p>Sample screenshot


7.      Say “Yes” and proceed, I will cover how to debug and set permissions, related to CLR store procedure in my next article named “Deploy and Debug CLR store procedure”.<o:p>

8.      Now in solution click on project and say “Add New Item”, following window appears where as select item of type “Store Procedure” and click on “ADD”.  (I will cover what “Test Script” folder hold in my next article named “Deploy and Debug CLR store procedure”)

<o:p>Sample screenshot


9.      Now in solution click on project and say “Add New Item”, following window appears where as select item of type “Store Procedure” and click on “ADD”.  (I will cover what “Test Script” folder hold in my next article named “Deploy and Debug CLR store procedure”)<o:p>

10.  Cheer up, you are about to write your first store procedure. Here I will show you simple demo of how to select records using CLR store procedure, and it goes here.<o:p>


    public static void GetRole()<o:p>


        // Put your code here<o:p>

SqlConnection conn = new SqlConnection("Context    Connection=true");<o:p>

SqlCommand cmd = new SqlCommand(@"Select * from dbo.Roles", conn);<o:p>




        SqlDataReader rdr = cmd.ExecuteReader();<o:p>






11.  Now select project from solution explorer and say deploy. Once deployed on SQL Server, go to SQL Server and fire following query and see the output. <o:p>

     USE [IRSDev]<o:p>

exec GetRole<o:p>

Output :

<o:p>Sample screenshot


12.  You may not able to deploy CLR store procedure in single go, since lot of permission issues. If so please refer my next article named “Deploy and Debug CLR store procedure”.<o:p>



This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Malpani Amol
Web Developer
India India
I am a .NET developer cum designer. I work for Zensar Technologies Ltd. Pune (INDIA), I consult, I develop, I debug and besides that I speak at conferences and user groups and I also write. I have written some articles on WCF, WF, SSIS, Compression decompression, CLR Store Procedure, SQL Server Reporting Services 2005, Extended grid and lot more to count.
Reach to me

You may also be interested in...


Comments and Discussions

QuestionHow to generate CRL desplyment script Pin
Jan Prochazka alias jena20-Oct-11 10:36
memberJan Prochazka alias jena20-Oct-11 10:36 
QuestionStore procedure or Stored procedure? Pin
Sam Hobbs10-Dec-10 8:59
memberSam Hobbs10-Dec-10 8:59 
GeneralExcellent Article Pin
Washington600074-Nov-06 19:53
memberWashington600074-Nov-06 19:53 
GeneralI thought it was a good article Pin
cykophysh3916-Oct-06 1:22
membercykophysh3916-Oct-06 1:22 
QuestionWhat hasn't been covered before Pin
Mark Nischalke15-Oct-06 5:40
memberMark Nischalke15-Oct-06 5:40 
AnswerRe: What hasn't been covered before Pin
Malpani Amol15-Oct-06 18:42
memberMalpani Amol15-Oct-06 18:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web03 | 2.8.180621.3 | Last Updated 15 Oct 2006
Article Copyright 2006 by Malpani Amol
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid