This documentation is archived and is not being maintained.

Walkthrough: Creating a Stored Procedure in Managed Code

You can now write stored procedures for SQL Server 2005 databases in managed code by 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 the following:

  • 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 correctly.

In order to complete this walkthrough, you need:

To create the new SQL Server project

  1. From the File menu, create a new project.

  2. Select SQL Server Project, name the project SQLCLRStoredProcedure and click OK. For more information, see How to: Create a SQL Server Project.

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, it will be listed in the Add Database Reference Dialog Box.


By default, the common language runtime (CLR) integration feature is off in Microsoft SQL Server. It 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. For more information, see Enabling CLR Integration.

To connect to the AdventureWorks sample database

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

To create the SQL Server stored procedure

  1. From the Project menu, click Add New Item.

  2. Select Stored Procedure in the Add New Item Dialog Box.

  3. Type InsertCurrency as the Name for the new stored procedure.

  4. Click Add.

  5. Replace the code in the Code Editor with the following:


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

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;
    public partial class StoredProcedures
        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);
                currencyCodeParam.Value = currencyCode;
                nameParam.Value = name;
                InsertCurrencyCommand.CommandText =
                    "INSERT Sales.Currency (CurrencyCode, Name, ModifiedDate)" +
                    " VALUES(@CurrencyCode, @Name, GetDate())";
                InsertCurrencyCommand.Connection = conn;
    #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
        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;

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 about creating test scripts see, How to: Edit the Test.sql Script to Run SQL Objects.

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

To deploy, and run the InsertCurrency stored procedure

  1. 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.

  2. 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'

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

    EXEC InsertCurrency_CPP 'AAA', 'Currency Test'
    SELECT * from Sales.Currency where CurrencyCode = 'AAA'
  3. Press F5 to build, deploy, and debug the stored procedure. For information about deploying without debugging, see How to: Deploy SQL Server Project Items to a SQL Server.

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