/* テスト用のサンプルテーブルを作成 */

CREATE TABLE [dbo].[Table_1] (

[ID] [int] IDENTITY(1,1) NOT NULL,

[UPD_DATE] [datetime2](7) NOT NULL,

[UPD_USER] [varchar](20) NOT NULL,

[CUST_NAME] [varchar](100) NOT NULL,

[ACTIVE_FLAG] [tinyint] NOT NULL,

CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED (

[ID] ASC

) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Table_1] ADD CONSTRAINT [DF_Table_1_UPD_DATE] DEFAULT (sysdatetime()) FOR [UPD_DATE]

GO

ALTER TABLE [dbo].[Table_1] ADD CONSTRAINT [DF_Table_1_ACTIVE_FLAG] DEFAULT ((1)) FOR [ACTIVE_FLAG]

GO

/* ユーザー定義テーブル型を作成 */

CREATE TYPE RowsetTableType AS TABLE (

UPD_USER VARCHAR(20),

CUST_NAME VARCHAR(100)

);

GO

/* テスト用ストアド作成 */

CREATE PROCEDURE INS_ROWSET_TABLE_DATA (

@IN_PARAM_TABLETYPE RowsetTableType READONLY

)

AS

BEGIN

SET NOCOUNT ON

INSERT INTO dbo.Table_1 (

UPD_USER,

CUST_NAME

)

SELECT UPD_USER, CUST_NAME FROM @IN_PARAM_TABLETYPE

SET NOCOUNT OFF

END

GO

-- テスト実行

DECLARE @IN_PARAM_TABLETYPE RowsetTableType;

INSERT INTO @IN_PARAM_TABLETYPE

SELECT

'00001','田中'

UNION ALL

SELECT

'00002','佐藤'

UNION ALL

SELECT

'00003','加藤'

DECLARE @RTN INT

EXEC @RTN = dbo.INS_ROWSET_TABLE_DATA @IN_PARAM_TABLETYPE

SELECT @RTN

SELECT * FROM dbo.Table_1

2017.04