Export (0) Print
Expand All

Walkthrough: Creating a Stored Procedure in Managed Code

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

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.

To create the new SQL CLR database project

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

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

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

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

  1. In Solution Explorer, select the project for which you want to configure properties.

  2. On the Project menu, click ProjectName Properties, where ProjectName is the name that you gave the SQL CLR database project.

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

  4. 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 noteImportant

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

  5. On the Database tab, in Assembly Owner, type the name of a user or role as the owner of the assembly.

    Important noteImportant

    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.

  6. On the File menu, click Save All.

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

To create the SQL Server stored procedure

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

  2. In the Add New Item dialog box, select Stored Procedure.

  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:

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

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

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

    NoteNote

    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.

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

Community Additions

ADD
Show:
© 2014 Microsoft