How to: Create and Run a CLR SQL Server Stored Procedure

Article
11/16/2012

Create SQL stored procedures by adding Stored Procedure items to SQL Server projects. After you successfully deploy to a computer running SQL Server, stored procedures created in managed code are called and executed like any other stored procedures.

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. For more information, see Enabling CLR Integration.

Note

The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Visual Studio Settings.

Creating SQL Server Stored Procedures

To create a SQL Server stored procedure

Open an existing SQL Server Project, or create a new one. For more information, see How to: Create a SQL Server Project.
From the Project menu, select Add New Item.
Select Stored Procedure in the Add New Item Dialog Box.
Type a Name for the new stored procedure.
Add code to run when the stored procedure is executed. See the example below.

Note

C++ examples must be compiled with the /clr:safe compiler option.
For Visual Basic and Visual C#, in Solution Explorer, open the TestScripts folder and double-click the Test.sql file.

For Visual C++, in Solution Explorer, open the debug.sql file.
Add code to the Test.sql (debug.sql for Visual C+) file to execute the stored procedure. See the second example below.
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.

Example

The following code example creates a stored procedure that inserts a record into the Currency table of the Adventure Works sample database. After creating the stored procedure, deploy it to the SQL Server. For more information, see How to: Deploy SQL Server Project Items to a SQL Server.

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.Parameters.Add(currencyCodeParam)
            InsertCurrencyCommand.Parameters.Add(nameParam)

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

            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);

            currencyCodeParam.Value = currencyCode;
            nameParam.Value = name;

            InsertCurrencyCommand.Parameters.Add(currencyCodeParam);
            InsertCurrencyCommand.Parameters.Add(nameParam);

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

            InsertCurrencyCommand.Connection = conn;

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

#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();
    }
};

Add code to execute and test your stored procedure to the Test.sql (debug.sql in Visual C+) file in the TestScripts folder in your project. For example, if you deployed a stored procedure, run it by calling EXEC <StoredProcedureName> and passing in any expected parameters. If your stored procedure does not return any values, insert additional code to verify data was affected by the stored procedure.

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

How to: Create and Run a CLR SQL Server Stored Procedure

Creating SQL Server Stored Procedures

To create a SQL Server stored procedure

Example

See Also

Tasks

Concepts

Reference

Other Resources

Additional resources