Walkthrough: Creating a Stored Procedure in Managed Code

Article
07/25/2011

You can write stored procedures for Microsoft SQL Server 2005 and SQL Server 2008 databases in managed code by using .NET Framework languages such as Visual Basic and Visual C#. Stored procedures that are written in managed code are called SQL Server Common Language Run-Time stored procedures or SQL CLR stored procedures.

You can create SQL stored procedures by adding Stored Procedure items to SQL CLR database projects. After you deploy stored procedures that you created in managed code, those procedures are called and executed like any other stored procedures.

Tasks illustrated in this walkthrough include the following:

Creating a new SQL CLR Database Project
Creating a stored procedure in managed code
Deploying the stored procedure to a SQL Server 2008 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

Prerequisites

In order to complete this walkthrough, you need:

A connection to the AdventureWorks2008 sample database running on SQL Server 2008. For more information, see the Microsoft SQL Server Product Samples page on the Microsoft web site.

Creating the Project

To create the new SQL CLR database project

From the File menu, create a new project.
Select SQL CLR Database Project, name the project SQLCLRStoredProcedure and click OK. For more information, see How to: Create a Project for Database Objects that Use SQL Server Common Language Run-time Integration.

Connecting to a SQL Server 2008 Database

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

To connect to the AdventureWorks sample database

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

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

Configuring the SQL CLR Database Project

After you create your SQL CLR database project, you might need to modify several project properties before you can successfully deploy your project.

To configure properties for the SQL CLR Database Project

In Solution Explorer, select the project for which you want to configure properties.
On the Project menu, click ProjectName Properties, where ProjectName is the name that you gave the SQL CLR database project.
On the Application tab, in Assembly name, specify the name that you want to give the assembly that is built from your SQL CLR database project.
On the Application tab, in Target Framework, click the version of the .NET Framework that is supported by the target version of SQL Server.

Important

SQL Server 2005 and SQL Server 2008 only support SQL Server projects that were built with the .NET Framework 2.0. If you try to deploy a SQL Server project to SQL Server 2005 or SQL Server 2008, an error appears: Deploy error (SQL01268): .NET SqlClient Data Provider: Msg 6218, Level 16, State 3, Line 1 CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database (where AssemblyName is the name of the assembly that you are deploying).
On the Database tab, in Assembly Owner, type the name of a user or role as the owner of the assembly.

Important

This value must be either the name of a role of which the current user is a member or the current user must have IMPERSONATE permission. If you do not specify an assembly owner, ownership is given to the current user. This setting corresponds to the AUTHORIZATION owner_name argument for the SQL Server CREATE ASSEMBLY statement. For more information, see CREATE ASSEMBLY (Transact-SQL) on the Microsoft Web site.
On the File menu, click Save All.

Creating the SQL Server Stored Procedure

After you create the SQL CLR database project, add a stored procedure to it.

To create the SQL Server stored procedure

On the Project menu, click Add New Item.
In the Add New Item dialog box, select Stored Procedure.
Type InsertCurrency as the Name for the new stored procedure.
Click Add.

Replace the code in the Code Editor with the following:

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

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 more information about how to create test scripts see, How to: Edit the Test.sql Script to Run Objects that use SQL Server Common Language Run-time Integration.

For more information about how to debug SQL, see Debugging SQL Database Objects.

To deploy, and run the InsertCurrency stored procedure

In Solution Explorer, expand the TestScripts folder and double-click the Test.sql file.

Note

You can specify other scripts as your default debug script. For more information, see How to: Edit the Test.sql Script to Run Objects that use SQL Server Common Language Run-time Integration.

Replace the code in the Test.sql file with the following code:

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

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

Press F5 to build, deploy, and debug the stored procedure. For information about how to deploy without debugging, see How to: Deploy SQL Server CLR Integration Database Project Items to a SQL Server.

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