Working with Managed Stored Procedure using Visual Studio 2005
Published: 23 Jul 2007
This article examines the creation of Managed Stored Procedure in .NET 2.0. It also explains the steps in creating CLR Stored Procedures using C#.
by Srinivas Jadhav
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 24350/ 76


Integration of SQL Server 2005 (formerly called as Yukon) with .NET CLR has given an opportunity for the .NET Professional to write Stored Procedure, Triggers, and UDF by using Managed Language such as C# and VB.NET.

Advantages of Managed Stored Procedure

·         Increased productivity

·         Significance performance gains

·         Ability to include the features of .NET code (e.g. arrays, sophisticated exception, and reusability of code)

·         Enhanced Safety and Security

·         Language options (like C#, VB.NET and VC++)

·         Stability and reliability

Steps in Creating Stored Procedure

First, launch Visual Studio 2005 and create a new SQL Server project by selecting File | New Project | Database.

Figure 1

Give a name to the project. I have called it ManagedProcedureTest.

Create a connection for the Database.

Figure 2

Click on "Test Connection" and click "OK."

Once the connection is established, then the popup appears.

If you wish to enable SQL/CLR debugging, then click Yes.

Figure 3

Right Click project and add a new file.

Figure 4

Select Stored Procedure and Click "Add."

The code appears as follows.

Listing 1: MyManagedProcedure.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
  public static void MyManagedProcedure()
  // Put your code here

Build Solution, Right Click Project and then click on Deploy.

Stored Procedure will be deployed to the Database.

Once the Deployment is over, refresh the Database>Stored Procedures.

Figure 5

You can see the dbo.MyManagedProcedure.

Sp_Help [ManagedStoredProcedure].[dbo].[MyManagedProcedure] will show that the Type is an assembly stored procedure. The next step is to execute the procedure.

Listing 2

EXECUTE [ManagedStoredProcedure].[dbo].[MyManagedProcedure]

If the CLR is not enabled, then make it enable by sp_configure statement.

Listing 3

sp_configure 'clr enabled', 1

Then again run the Stored Procedure.

Listing 4

EXECUTE [ManagedStoredProcedure].[dbo].[MyManagedProcedure].

The result will give nothing as MyManagedProcedure does not contain any code.

Store the Procedure with the Input parameter.

Now we will try to access the employee table and do some simple operations to it.

Create an Employee table.

Listing 5

USE [ManagedStoredProcedure]
/****** Object:  Table [dbo].[Employee]    
Script Date: 06/06/2007 16:09:35 ******/
CREATE TABLE [dbo].[Employee](
      [EmpID] [intNOT NULL,
      [EmpName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Salary] [numeric](18, 0) NULL,
      [EmpID] ASC

Insert the records in the table.

I will pass the Input Parameter as Salary and get the employee details.

Add the following code in the MyManagedProcedure() method.

Listing 6

public static void MyManagedProcedure(double sal)
  using(SqlConnection conn = new SqlConnection("context connection=true"))
    SqlCommand cmd = new SqlCommand(
      "SELECT * FROM EMPLOYEE WHERE SALARY >= @sal", conn);
    SqlParameter paramSal = new SqlParameter();
    paramSal.Value = sal;
    paramSal.Direction = ParameterDirection.Input;
    paramSal.DbType = DbType.Int32;
    paramSal.ParameterName = "@sal";

Compile the code and deploy it.

In the SQL Server, refresh the Stored Procedure.

You can see one Input parameter @sal (float,Input,No default).

Execute the Stored Procedure.

Listing 7

DECLARE @sal float
SET @sal = 2000
EXECUTE [ManagedStoredProcedure].[dbo].[MyManagedProcedure] 

You will get the result for the employees whose salary is greater then 2000.

Store Procedure with Input & Output parameters.

Now, I want the Out parameter from the Managed Stored Procedure.

Do the following changes to the existing method.

I want the count of the number employees as an out parameter for the given salary.

Add the parameter "cnt" as ref to get the Count of number of employees for the given salary.

Listing 8

public static void MyManagedProcedure(double sal,ref int cnt)

Change the SqlCommand object.

Listing 9

SqlCommand cmd = new SqlCommand(
    "SELECT @cnt=COUNT(*) FROM EMPLOYEE WHERE SALARY >= @sal",conn);

Create another SqlParameter for Count.

Listing 10

SqlParameter paramCount = new SqlParameter();
paramCount.Direction = ParameterDirection.Output;
paramCount.DbType = DbType.Int32;
paramCount.ParameterName = "@cnt";
Add SqlParameter to the SqlCommand object.
At the end add the following code after SqlContext.Pipe.ExecuteAndSend(cmd);
cnt = Convert.ToInt32(paramCount.Value);        

In SQL Server, refresh the Stored Procedure.

You can see one Input parameter @sal (float,Input,No default) and @sal (float,Input/Output,No default).

Execute the Stored Procedure.

Listing 11

DECLARE @sal float
DECLARE @cnt int
SET @sal = 2000
SET @cnt = 0
EXECUTE [ManagedStoredProcedure].[dbo].[MyManagedProcedure] 
  ,@cnt OUTPUT
Print @cnt


When the execution of the code is requested as part of a user connection, access to the context of the caller from the code running in the server is required.

SqlContext provides access to the following components.

Pipe: It is an object through which the results flow to the client. It allows the caller to send result sets, messages and the results of executing commands back to the client.

TriggerContext: It is an object which can only be retrived from within a CLR trigger. It gets the trigger context used to provide the caller with information about what caused the trigger to fire, and a map of the columns that were updated.

IsAvailable: Specifies whether the calling code is running within the SQL Server and if the context connection can be accessed.

WindowsIdentity: It is used to retrieve the windows identity of the caller.

Debugging Managed Stored Procedure

You can now debug the CLR Stored Procedure the same way as the T-SQL Stored Procedure.

Following are the steps used to debug it.

In the Solution Explorer, right click the Test Scripts and Add Test Script; Test1.sql is added.

Rename it to MyManagedProcedure.sql.

Now you add the following code.

Listing 12

DECLARE @sal float
DECLARE @cnt int
SET @sal = 2200
SET @cnt = 0
EXECUTE [ManagedStoredProcedure].[dbo].[MyManagedProcedure] 
  ,@cnt OUTPUT
Print @cnt

Set the break point in the code.

Open Server Explorer View > Server Explorer.

Right Click Database and Apply Allow SQL/CLR Debugging

Debug > Start Debugging. The Yellow Pointer appears at the break point.

You can view the Output.


From this article we came to learn how to create a CLR (Managed) Stored Procedure. We are able to access Input and Output parameters. You also learned the advantages of Managed Stored Procedure and how to debug a Managed Stored procedure like the T-SQL Stored Procedure.

User Comments

No comments posted yet.

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2021  |  Page Processed at 2021-12-05 6:19:03 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search