SQL Server のテーブル型の使い方を整理してみた

一応仕事で SQL Server は触っていて、テーブル型についても仕事で必要になった範囲でなんとなく使ってたんですけど、ちょっと応用したことをしようとしたらどう書けばいいのかわからなくなったので、自分の中で整理することにしました。

テーブル型変数の宣言

DECLARE @Table TABLE(
  Id int NOT NULL,
  Name varchar(20) NOT NULL
)

テーブル型変数を使った SELECT

SELECT * FROM @Table

テーブル型変数への INSERT

INSERT INTO @Table VALUES(1, 'Hoge')

テーブル型変数の UPDATE

UPDATE @Table SET name = name + '1'

テーブル型変数の DELETE

DELETE FROM @Table

インラインテーブル値関数

RETURNS には TABLE とだけ書いて、ボディには BEGIN-END を書かず、RETURN に戻り値のクエリを書く。

CREATE FUNCTION dbo.InlineTableValued()
RETURNS TABLE
AS
RETURN
  SELECT 1 Id, 'Hoge' Name
  UNION ALL
  SELECT 2 Id, 'Fuga' Name
  UNION ALL
  SELECT 3 Id, 'Homu' Name
GO

SELECT * FROM dbo.InlineTableValued()

@Table

id	name
1	Hoge
2	Fuga
3	Homu

複数ステートメントのテーブル値関数

RETURNS に戻り値として返すテーブル型変数を宣言して、ボディの最後には RETURN だけ書く。

CREATE FUNCTION dbo.MultiStatementTableValued()
RETURNS @Table TABLE (
  Id int NOT NULL,
  Name varchar(20) NOT NULL
)
AS
BEGIN

INSERT INTO @Table VALUES(1, 'Hoge')
INSERT INTO @Table VALUES(2, 'Fuga')
INSERT INTO @Table VALUES(3, 'Homu')

RETURN

END
GO

SELECT * FROM dbo.MultiStatementTableValued()

@Table

id	name
1	Hoge
2	Fuga
3	Homu

プロシージャ内で発行したクエリの結果をテーブル型変数に INSERT する

CREATE PROCEDURE dbo.PROC1
AS
BEGIN
  SELECT 1 Id, 'Hoge' Name
  UNION ALL
  SELECT 2 Id, 'Fuga' Name
  UNION ALL
  SELECT 3 Id, 'Homu' Name
RETURN 0
END
GO

DECLARE @Table TABLE(
  Id int NOT NULL,
  Name varchar(20) NOT NULL
)

INSERT INTO @Table EXECUTE dbo.PROC1

SELECT * FROM @Table

@Table

id	name
1	Hoge
2	Fuga
3	Homu