SQL Server ストアドプロシージャのreturnを取得する

この文書はVisual Studio 2005 RC(.NET2.0)をベースに記述されています が、Visual Studio 2003でも利用できます。

SQL ServerのストアドプロシージャはOracleのPL/SQLよりさまざまな面でプログラムと連携しやすくなっています。

SQL Serverのストアドプロシージャからは以下のような構文でreturnによる戻り値を返せます。

CREATE PROCEDURE [存在チェック]
  @id int
AS
BEGIN
  SET NOCOUNT ON;
  if ( @id = 1 )
    return 1
  else
    return 0

end

戻り値を取得する方法は簡単で、パラメータに1つ特殊なパラメータを設定するだけです。

using (SqlCommand com = new SqlCommand())
{
  com.CommandType = System.Data.CommandType.StoredProcedure;
  com.CommandText = "存在チェック";
* com.Parameters.Add("RetValue", System.Data.SqlDbType.Int, 4);
* com.Parameters["RetValue"].Direction = System.Data.ParameterDirection.ReturnValue;
  com.Parameters.Add("@id", System.Data.SqlDbType.Int);
  com.Parameters["@id"].Value = 1;

  com.Connection = this.connection;
  try
  {
    this.connection.Open();
    com.ExecuteNonQuery();
    Assert.AreEqual<int?>(1, (int?)com.Parameters["RetValue"].Value);
  }
  finally
  {
    if (this.connection.State != System.Data.ConnectionState.Closed)
    {
      this.connection.Close();
    }
  }
}

*米印を打っている行がその行です。

ParameterDirection.ReturnValueが味噌になります。

詳しくは以下のKBなども参照してください。

[HOWTO] パラメータ化されたストアド プロシージャを ADO.NET と Visual C# .NET を使って呼び出す方法
http://support.microsoft.com/kb/310070/ja

パラメータ化されたストアド プロシージャを ADO.NET と Visual Basic .NET を使って呼び出す方法
http://support.microsoft.com/kb/308049/ja

[HOWTO] パラメータ化されたストアド プロシージャを ADO.NET と Visual C++ .NET を使って呼び出す方法
http://support.microsoft.com/kb/310071/ja