SQL Server ストアドでユーザー定義テーブル型を引数に使う

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

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