How to: Create and Run a SQL Server Stored Procedure by using Common Language Run-time Integration

Switch View :

ScriptFree

Visual Studio 2010

Create SQL stored procedures by adding Stored Procedure items to SQL Server Common Language Run-time (SQL CLR) database projects. After you successfully deploy to a computer that is running SQL Server, stored procedures created in managed code are called and executed like any other stored procedures.

Note
Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.

Creating SQL Server Stored Procedures

To create a SQL Server stored procedure

Open an existing SQL CLR database project, or create a new one. For more information, see How to: Create a Project for Database Objects that Use SQL Server Common Language Run-time Integration.
On 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 first example that follows this procedure.

In Solution Explorer, open 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.

Add code to the Test.sql file to execute the stored procedure. See the second example that follows this procedure.

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.

Important
SQL Server 2005 and SQL Server 2008 only support SQL Server projects that were built with the 2.0, 3.0, or 3.5 version of the .NET Framework. 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). For more information, see How to: Create a Project for Database Objects that Use SQL Server Common Language Run-time Integration.

Important

SQL Server 2005 and SQL Server 2008 only support SQL Server projects that were built with the 2.0, 3.0, or 3.5 version of the .NET Framework. 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). For more information, see How to: Create a Project for Database Objects that Use SQL Server Common Language Run-time Integration.

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 CLR Integration Database Project Items to a SQL Server.

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

Add code to execute and test your stored procedure to the Test.sql 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 returns no values, insert additional code to verify that data was affected by the stored procedure.

Note
If you created the sample by using Visual C#, substitute InsertCurrency_CS for the stored procedure name in the following sample.

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

Community Content

StevenPo

Must declare the scalar variable "@CurrencyCode"

VB2008 Sql Server Project deployed to Sql Server 2005 AdventureWorks database.

Used the exact VB code listed and the exact query listed (and different variations of it.) (see below error message.)

Any help anyone could provide would be greatly appreciated! Thanks, Jeff

ERROR MESSAGE:

Msg 6522, Level 16, State 1, Procedure InsertCurrency, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "InsertCurrency":
System.Data.SqlClient.SqlException: Must declare the scalar variable "@CurrencyCode".
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.DispatchMessages(Boolean ignoreNonFatalMessages)
at Microsoft.SqlServer.Server.SmiEventSink_Default.DispatchMessages(Boolean ignoreNonFatalMessages)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at LearnCLRIntegration.StoredProcedures.InsertCurrency(SqlString currencyCode, SqlString name)
.

VB CODE:

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, GetDate())"

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

SQL SERVER 2005 QUERY:

EXECUTE InsertCurrency 'AAA', 'AAA'

StevenPo - MSFT: I was able to successfully complete the procedures in this topic both against a SQL Server 2008 server running AdventureWorks2008 and against a SQL Server 2005 server running AdventureWorks2005. In the code that you pasted above, you left out the following lines of code:
InsertCurrencyCommand.Parameters.Add(currencyCodeParam)
InsertCurrencyCommand.Parameters.Add(nameParam)
These lines follow nameParam.Value = name. Without those lines, you are not passing any parameters when you execute the InsertCurrencyCommand. If you run into additional problems, you might want to try the forums: http://social.msdn.microsoft.com/Forums/en-US/vstsdb/threads

How to: Create and Run a SQL Server Stored Procedure by using Common Language Run-time Integration

To create a SQL Server stored procedure

Tasks

Reference

Concepts

Other Resources