Stored Procedure @ Transact-SQL (SQL Server).sql

## Stored Procedure @ Transact-SQL (SQL Server).sql
-- ユーザー定義テーブル型
CREATE TYPE [dbo].[IntValueTableType] AS TABLE ([Value] INT NULL);
GO

-- ストアドプロシージャ
CREATE PROCEDURE [dbo].[SummaryValueProc]
    -- 入力パラメタ
    @InPut int,
    -- 出力パラメタ
    @OutPut int OUTPUT,
    -- テーブル値パラメタ
    @Values IntValueTableType READONLY
AS
BEGIN
    -- 出力パラメタ
    SELECT @OutPut = SUM(Value) FROM @Values;

    -- 結果セット * 2
    SELECT * FROM [dbo].[Shippers];
    SELECT * FROM [dbo].[Suppliers];

    -- 戻り値
    RETURN @InPut * 2;
END ;
GO

## StoredProcedure by OpenTouryo.cs
TestReturnValue testReturn = new TestReturnValue();
this.ReturnValue = testReturn;

// ↓業務処理-----------------------------------------------------
DataTable dt = null;
DataRow row = null;

CmnDao cmnDao = new CmnDao(this.GetDam(), CommandType.StoredProcedure);

cmnDao.SQLText = "dbo.SummaryValueProc";
cmnDao.SetParameter("InPut", 7, null, 0, ParameterDirection.Input);
cmnDao.SetParameter("OutPut", null, SqlDbType.Int, 0, ParameterDirection.Output);
cmnDao.SetParameter("Return", null, SqlDbType.Int, 0, ParameterDirection.ReturnValue);

dt = new DataTable();
dt.Columns.Add("Values", typeof(int));

row = dt.NewRow();
row[0] = 1;
dt.Rows.Add(row);
row = dt.NewRow();
row[0] = 2;
dt.Rows.Add(row);

cmnDao.SetParameter("Values", dt, SqlDbType.Structured);

IDataReader idr = cmnDao.ExecSelect_DR();

DataTable dt1 = new DataTable();
dt1.Load(idr);
DataTable dt2 = new DataTable();
dt2.Load(idr);

int i = (int)cmnDao.GetParameter("OutPut");
int j = (int)cmnDao.GetParameter("Return");