方法: 共通言語ランタイム統合機能を使用して SQL Server ストアドプロシージャを作成および実行する

[アーティクル]
08/09/2011

SQL Server 共通言語ランタイム (SQL CLR) データベースプロジェクトに [ストアドプロシージャ] 項目を追加して、SQL ストアドプロシージャを作成します。 SQL Server を実行しているコンピューターに正しく配置すると、マネージコードで作成されたストアドプロシージャは、他のストアドプロシージャと同様に呼び出され、実行されます。

注意

使用している設定またはエディションによっては、ヘルプの記載と異なるダイアログボックスやメニューコマンドが表示される場合があります。設定を変更するには、[ツール] メニューの [設定のインポートとエクスポート] をクリックします。詳細については、「設定の操作」を参照してください。

SQL Server のストアドプロシージャの作成

SQL Server のストアドプロシージャを作成するには

既存の SQL CLR データベースプロジェクトを開くか、または新しいプロジェクトを作成します。詳細については、「方法: SQL Server 共通言語ランタイム統合機能を使用するデータオブジェクトのプロジェクトを作成する」を参照してください。
[プロジェクト] メニューの [新しい項目の追加] をクリックします。
[新しい項目の追加] ダイアログボックスで、[ストアドプロシージャ] をクリックします。
新しいストアドプロシージャに [ファイル名] を指定します。
ストアドプロシージャの実行時に実行するコードを追加します。下記の最初の例を参照してください。
ソリューションエクスプローラーで、[TestScripts] フォルダーを開き、Test.sql ファイルをダブルクリックします。

注意

既定のデバッグスクリプトに他のスクリプトを指定することもできます。詳細については、「方法: Test.sql スクリプトを編集して SQL Server 共通言語ランタイム統合機能を使用するオブジェクトを実行する」を参照してください。
ストアドプロシージャを実行するコードを Test.sql ファイルに追加します。この手順に続く 2 番目の例を参照してください。
F5 キーを押して、ストアドプロシージャをビルド、配置、およびデバッグします。デバッグせずに配置する方法については、「方法: SQL CLR データベースプロジェクト項目を SQL Server に配置する」を参照してください。

重要

2.0、3.0、または 3.5 バージョンの .NET Framework でビルドした SQL Server プロジェクトをサポートするのは、SQL Server 2005 および SQL Server 2008 のみです。展開すると、SQL ServerプロジェクトSQL Server 2005またはSQL Server 2008、エラーが表示されます。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(ここでAssemblyNameは、展開するアセンブリの名前です)。詳細については、「方法: SQL Server 共通言語ランタイム統合機能を使用するデータオブジェクトのプロジェクトを作成する」を参照してください。
結果を表示、[出力] ウィンドウ選択出力元の表示：データベース出力。

使用例

Adventure Works サンプルデータベースの Currency テーブルにレコードを挿入するストアドプロシージャを作成するコード例を次に示します。このストアドプロシージャの作成後に、SQL Server に配置します。詳細については、「方法: SQL CLR データベースプロジェクト項目を 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();
        }
    }
}

ストアドプロシージャの実行とテストを行うコードを、プロジェクトの TestScripts フォルダーにある Test.sql ファイルに追加します。たとえば、ストアドプロシージャを配置した場合、EXEC <StoredProcedureName> を呼び出して、必要なパラメーターをすべて渡すことで実行します。ストアドプロシージャから値がまったく返されない場合、データがストアドプロシージャの影響を受けたことを確認するために追加のコードを挿入します。

注意

Visual C# を使用してサンプルを作成した場合、次のサンプルでストアドプロシージャの名前の InsertCurrency_CS を置き換えます。

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

参照

方法: 共通言語ランタイム統合機能を使用して SQL Server ストアド プロシージャを作成および実行する

SQL Server のストアド プロシージャの作成

SQL Server のストアド プロシージャを作成するには

使用例

参照

処理手順

参照

概念

その他の技術情報

その他のリソース

方法: 共通言語ランタイム統合機能を使用して SQL Server ストアドプロシージャを作成および実行する

SQL Server のストアドプロシージャの作成

SQL Server のストアドプロシージャを作成するには