2016年10月10日

SQL ServerのストアドプロシージャからOUTPUTと戻り値を受け取る



SQL ServerのストアドプロシージャからOUTPUTと戻り値を受け取る方法です。

ちなみに今回もSQL Serverにあらかじめ以下のようなテーブルを作成しています。

テーブル名:T_Animals

列名
[ID] [int] NOT NULL
[Name] [nvarchar](50) NULL
[Type] [nvarchar](50) NULL

格納されてるデータ
ID Name Type
1 たま
2 ピーター うさぎ
3 トム
4 お父さん
5 ふてニャン
6 プー くま
7 ファーファ くま
8 ラスカル アライグマ
9 サトちゃん
10 チーバくん
11 パトラッシュ

また、usp_get_outputという名前のストアドプロシージャをあらかじめ作っておきます。
USE [TESTDB]
GO
CREATE PROCEDURE [dbo].[usp_get_output]
 @ID int, 
 @Name nvarchar(50) OUTPUT
AS
BEGIN
 SET NOCOUNT ON;

 SET @Name = (SELECT [Name]
 FROM [dbo].[T_Animals]
 WHERE [ID] = @ID)

 RETURN 0
END

パラメータにOUTPUTがついてるものがありますが、これがアウトプット引数で、このパラメータによりデータを受け取ることができます。
また、終わりにRETURN 0とありますが、ここで指定してる「0」が戻り値になります。

C#
string ConnectionString = string.Empty;
ConnectionString = @"Data Source=localhost\MSSQLSERVER;Initial Catalog=TESTDB;Integrated Security=True";

var cnn = new SqlConnection(ConnectionString);
cnn.Open();

var cmd = new SqlCommand();
cmd.Connection = cnn;

// コマンド文字列の解釈方法を指定
cmd.CommandType = System.Data.CommandType.StoredProcedure;

// INPUT
int inputId = 5;

// データソースで実行するストアドプロシージャを指定
cmd.CommandText = "[usp_get_output]";
cmd.Parameters.Clear();
cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = inputId;

// OUTPUT
cmd.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar, 50);
cmd.Parameters["@Name"].Direction = System.Data.ParameterDirection.Output;

// RETURN
cmd.Parameters.Add("ReturnValue", System.Data.SqlDbType.Int);
cmd.Parameters["ReturnValue"].Direction = System.Data.ParameterDirection.ReturnValue;

cmd.ExecuteNonQuery();

Console.WriteLine("インプット:" + inputId.ToString());

string name = cmd.Parameters["@Name"].Value.ToString();
Console.WriteLine("アウトプット:" + name);

int rtn = (int)cmd.Parameters["ReturnValue"].Value;
Console.WriteLine("戻り値:" + rtn.ToString());

cmd.Dispose();
cnn.Close();

VB
Dim ConnectionString As String = String.Empty
ConnectionString = "Data Source=localhost\MSSQLSERVER;Initial Catalog=TESTDB;Integrated Security=True"

Dim cnn = New SqlConnection(ConnectionString)

cnn.Open()

Dim cmd = New SqlCommand()

cmd.Connection = cnn

cmd.CommandType = System.Data.CommandType.StoredProcedure

'INPUT
Dim inputId As Integer = 5

'データソースで実行するストアドプロシージャを指定
cmd.CommandText = "[usp_get_output]"
cmd.Parameters.Clear()
cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = inputId

'OUTPUT
cmd.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar, 50)
cmd.Parameters("@Name").Direction = System.Data.ParameterDirection.Output

'RETURN
cmd.Parameters.Add("ReturnValue", System.Data.SqlDbType.Int)
cmd.Parameters("ReturnValue").Direction = System.Data.ParameterDirection.ReturnValue

cmd.ExecuteNonQuery()

Console.WriteLine("インプット:" & inputId.ToString())

Dim name As String = cmd.Parameters("@Name").Value.ToString()
Console.WriteLine("アウトプット:" & name)

Dim rtn As Integer = DirectCast(cmd.Parameters("ReturnValue").Value, Integer)
Console.WriteLine("戻り値:" + rtn.ToString())

cmd.Dispose()
cnn.Close()

結果は下記のようになります。
インプット:5
アウトプット:ふてニャン
戻り値:0



スポンサーリンク