Walkthrough: Creating a Stored Procedure in Managed Code

Switch View :

Data Access in Client and Middle-Tier Programming

Stored procedures for SQL Server 2005 databases can now be written in managed code using .NET Framework languages such as Visual Basic, C#, and C++. Stored procedures written in managed code are called CLR stored procedures.

You can create SQL stored procedures by adding Stored Procedure items to SQL Server projects. After successfully deploying to a SQL Server, stored procedures created in managed code are called and executed like any other stored procedures.

Tasks illustrated in this walkthrough include:

Creating a new Windows Application project.
Creating a stored procedure in managed code.
Deploying the stored procedure to a SQL Server 2005 database.
Creating a script to test the stored procedure on the database.
Querying data in the database to confirm that the stored procedure executes properly.

Prerequisites

In order to complete this walkthrough, you need:

A connection to the AdventureWorks sample database running on SQL Server 2005. For more information, see How to: Install Sample Databases.

Creating the Project

To create the new SQL Server project

From the File menu, create a new project.
Select SQL Server Project, name the project SQLCLRStoredProcedure and click OK. For more information, see How to: Create a SQL Server Project.

Connecting to a SQL Server 2005 Database

This walkthrough requires a connection to the AdventureWorks sample database running on SQL Server 2005. If a connection to the AdventureWorks sample database is available in Server Explorer, then it will be listed in the Add Database Reference Dialog Box.

Note
The common language runtime (CLR) integration feature is off by default in Microsoft SQL Server and must be enabled in order to use SQL Server project items. To enable CLR integration, use the clr enabled option of the sp_configure stored procedure. From more information, see Enabling CLR Integration.

To connect to the AdventureWorks sample database

Connect to the AdventureWorks sample database included with SQL Server 2005 by completing the Add Connection dialog box. For more information, see Add/Modify Connection (Microsoft SQL Server).

-or-
Select an existing connection to the AdventureWorks sample database in the Add Database Reference dialog box. For more information, see Add Database Reference Dialog Box.

Creating the SQL Server Stored Procedure

After creating the SQL Server project, add a stored procedure to it.

To create the SQL Server stored procedure

From the Project menu, choose Add New Item.
Select Stored Procedure in the Add New Item Dialog Box.
Type InsertCurrency as the Name for the new stored procedure.
Click Add.

Replace the code in the Code Editor with the following:

Note
C++ examples must be compiled with the /clr:safe compiler option.

Visual Basic

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures

    <SqlProcedure()> _
    Public Shared Sub InsertCurrency( _
        ByVal currencyCode As SqlString, ByVal name As SqlString)

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

            Dim InsertCurrencyCommand As New SqlCommand()
            Dim currencyCodeParam As New SqlParameter("@CurrencyCode", SqlDbType.NVarChar)
            Dim nameParam As New SqlParameter("@Name", SqlDbType.NVarChar)

            currencyCodeParam.Value = currencyCode
            nameParam.Value = name


            InsertCurrencyCommand.CommandText = _
                "INSERT Sales.Currency (CurrencyCode, Name, ModifiedDate)" & _
                " VALUES(@CurrencyCode, @Name)"

            InsertCurrencyCommand.Connection = conn

            conn.Open()
            InsertCurrencyCommand.ExecuteNonQuery()
            conn.Close()
        End Using
    End Sub
End Class

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
    [SqlProcedure()]
    public static void InsertCurrency_CS(
        SqlString currencyCode, SqlString name)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand InsertCurrencyCommand = new SqlCommand();
            SqlParameter currencyCodeParam = new SqlParameter("@CurrencyCode", SqlDbType.NVarChar);
            SqlParameter nameParam = new SqlParameter("@Name", SqlDbType.NVarChar);



            InsertCurrencyCommand.CommandText =
                "INSERT Sales.Currency (CurrencyCode, Name, ModifiedDate)" +
                " VALUES(@CurrencyCode, @Name)";

            InsertCurrencyCommand.Connection = conn;

            conn.Open();
            InsertCurrencyCommand.ExecuteNonQuery();
            conn.Close();
        }
    }
}

C++

#include "stdafx.h"

#using <System.dll>
#using <System.Data.dll>
#using <System.Xml.dll>

using namespace System;
using namespace System::Data;
using namespace System::Data::Sql;
using namespace System::Data::SqlClient;
using namespace System::Data::SqlTypes;
using namespace Microsoft::SqlServer::Server;

// In order to debug your Stored Procedure, add the following to your debug.sql file:
//
// EXEC InsertCurrency_CPP 'AAA', 'Currency Test'
// SELECT * FROM Sales.Currency WHERE CurrencyCode = 'AAA'

public ref class StoredProcedures
{
public:
    [SqlProcedure]
    static void InsertCurrency_CPP(SqlString currencyCode, SqlString name)
    {
        SqlConnection ^conn = gcnew SqlConnection("context connection=true");

        SqlCommand ^insertCurrencyCommand = gcnew SqlCommand();
        SqlParameter ^currencyCodeParam =
            gcnew SqlParameter("@CurrencyCode", SqlDbType::NVarChar);
        SqlParameter ^nameParam =
            gcnew SqlParameter("@Name", SqlDbType::NVarChar);

        insertCurrencyCommand->CommandText =
            "insert Sales.Currency(CurrencyCode, Name, ModifiedDate)" +
            " values(@CurrencyCode, @Name)";
        insertCurrencyCommand->Connection = conn;

        conn->Open();
        insertCurrencyCommand->ExecuteNonQuery();

        conn->Close();
    }
};

Deploying, Executing, and Debugging the Stored Procedure

After you create a new stored procedure, it can be built, deployed to the SQL server, and debugged by pressing F5. First, in the Test.sql file found in the TestScripts folder of your project, add code to execute and test your stored procedure. For Visual C++, this file is named debug.sql. For more information on creating test scripts see, How to: Edit the Test.sql Script to Run SQL Objects.

For more information on debugging SQL, see Debugging SQL Database Objects.

To deploy, and run the InsertCurrency stored procedure

For Visual Basic and Visual C#, in Solution Explorer, expand the TestScripts folder and double-click the Test.sql file.

For Visual C++, in Solution Explorer, double-click the debug.sql file.

Replace the code in the Test.sql (debug.sql in Visual C++) file with the following code:

EXEC InsertCurrency 'AAA', 'Currency Test'
SELECT * from Sales.Currency where CurrencyCode = 'AAA'

Press F5 to build, deploy, and debug the stored procedure. For information on deploying without debugging, see How to: Deploy SQL Server Project Items to a SQL Server.

View the results in the Output window and select Show output from: Database Output.

Tags :

Community Content

Thomas Lee

deliberate mistake to hone debug skills?

Maybe I missed the point of the sample code, and it is intentionally wrong in order to demonstrate the super debugging environment. If not, the following code is more likely to work:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

  
public partial class StoredProcedures {
 [SqlProcedure]
 public static void InsertCurrency(SqlString currencyCode, SqlString name) {
  using(SqlConnection cn = new SqlConnection("context connection=true")) {
   SqlCommand cmd = new SqlCommand();
   cmd.Parameters.Add("@CurrencyCode", SqlDbType.NVarChar).Value = currencyCode;
   cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = name;
   cmd.CommandText = "INSERT Sales.Currency (CurrencyCode, Name) VALUES(@CurrencyCode, @Name)";
   cmd.Connection = cn;
   cn.Open();
   cmd.ExecuteNonQuery();

   cn.Close();
  }
 }
};

hey ho.

Tags : contentbug?

Sashidhar Kokku

Extra settings for the walkthrough

The default configuration of sql-server does not have the "clr enabled" option as true. Without it being true, your result-set will always be empty. First order of business is to enable it. Simplest way to do it would beto run the following script.

EXEC sp_configure 'show advanced options' , '1';

go

reconfigure;

go

EXEC sp_configure 'clr enabled' , '1'

go

reconfigure;

-- Turn advanced options back off

EXEC sp_configure 'show advanced options' , '0';

go

Having done that, you should be able to get the resultsets in the output window.
If even that does not work, you want to use SqlPipe.so, the code in the sample above translates to :

SqlPipe pipe = SqlContext.Pipe;


using (SqlConnection conn = new SqlConnection("context connection=true"))

SqlCommand InsertCurrencyCommand = new SqlCommand();

SqlParameter currencyCodeParam = new SqlParameter("@CurrencyCode", SqlDbType.NVarChar);

SqlParameter nameParam = new SqlParameter("@Name", SqlDbType.NVarChar);


InsertCurrencyCommand.CommandText =

"INSERT Sales.Currency (CurrencyCode, Name, ModifiedDate)" +

" VALUES(@CurrencyCode, @Name)";


InsertCurrencyCommand.Connection = conn;


conn.Open();

// InsertCurrencyCommand.ExecuteNonQuery();

pipe.ExecuteAndSend(cmd);

conn.Close();

Tags : clr enabled sqlpipe

irsteph

parameter sizes

It seems to ignore any size you assign to a parameter - or am I doing something wrong?

Whether I set ... new SqlParameter("@CurrencyCode", SqlDbType.NVarChar, 20); .... or leave the 20 out, it creates a 4000 sized parameter.

Tags :

Thomas Lee

Re: Parameter Sizes

It is not ignoring it, it will ether throw an error or clip the string length to 20 on that line (I dont remember offhand); it sounds as if you are wanting though is to change the external interface, which is determined by the function declaration parameters, in the example it only indicates it will be a SqlString, to limit the size to 20 you would modify the function declaration like this:

public static void InsertCurrency_CPP( [SqlFacet(MaxSize=20)] SqlStringcurrencyCode, [SqlFacet(MaxSize=20)] SqlString name){

Tags : contentbug

To create the new SQL Server project

To connect to the AdventureWorks sample database

To create the SQL Server stored procedure

To deploy, and run the InsertCurrency stored procedure

Tasks

Reference

Concepts

Other Resources