ユーザー定義 テーブル値関数で、返すテーブル値の項目名と型の簡潔な記述方法は？

Question

Accessはよく使用しているのですが、SQLサーバーは初心者です。よろしくお願いします。

ユーザー定義テーブル値関数の定義の記述方法で教えてください。

まず、

CREATE FUNCTION dbo.期間放射能結果
(
	@期間開始 datetime ='2001/01/01',
	@期間終了 datetime ='2200/12/31',
	@検体情報 nvarchar(55) =NULL
)
RETURNS @returntable TABLE
(
    [公開] BIT,
	[検査番号] NVARCHAR (20),
	[消費材CD] NVARCHAR (50),
	[検体名] NVARCHAR (50),
	[日付意味] NVARCHAR (4),
	[日付] DATETIME,
	[測定日] DATETIME,
	[測定機関] NVARCHAR (50),
	[重量ｋｇ] FLOAT,
	[測定分]            INT,
	[種別]              NVARCHAR (5),
	[ヨウ素131]         NVARCHAR (15),
	[セシウム134]       NVARCHAR (15),
	[セシウム137]       NVARCHAR (15),
	[セシウム合計]      NVARCHAR (15),
	[ヨウ素131下限]     NVARCHAR (15),
	[セシウム134下限]   NVARCHAR (15),
	[セシウム137下限]   NVARCHAR (15),
	[コメント]          NVARCHAR (MAX),
	[備考]              NVARCHAR (50),
	[値ヨウ素131]         FLOAT,
	[値セシウム134]       FLOAT,
	[値セシウム137]       FLOAT,
	[値セシウム合計]      FLOAT,
	[値ヨウ素131下限]     FLOAT,
	[値セシウム134下限]   FLOAT,
	[値セシウム137下限]   FLOAT,
	[値セシウム合計下限]  FLOAT
)
AS
BEGIN
	IF @検体情報 is null or @検体情報=''
	BEGIN
		INSERT @returntable
		SELECT 
		    [公開],
			[検査番号],
			[消費材CD],
			[検体名],
			[日付意味],
			[日付],
			[測定日],
			[測定機関],
			[重量ｋｇ],
			[測定分],
			[種別],
			[ヨウ素131],
			[セシウム134],
			[セシウム137],
			[セシウム合計],
			[ヨウ素131下限],
			[セシウム134下限],
			[セシウム137下限],
			[コメント],
			[備考],
			[値ヨウ素131],
			[値セシウム134],
			[値セシウム137],
			[値セシウム合計],
			[値ヨウ素131下限],
			[値セシウム134下限],
			[値セシウム137下限],
			[値セシウム合計下限]
		FROM dbo.Web放射能結果
		WHERE 測定日>=@期間開始 and 測定日<(@期間終了+1);
	END
	ELSE
	BEGIN
		SET @検体情報= N'%' + @検体情報 + N'%'
		INSERT @returntable
		SELECT
		    [公開],
			[検査番号],
			[消費材CD],
			[検体名],
			[日付意味],
			[日付],
			[測定日],
			[測定機関],
			[重量ｋｇ],
			[測定分],
			[種別],
			[ヨウ素131],
			[セシウム134],
			[セシウム137],
			[セシウム合計],
			[ヨウ素131下限],
			[セシウム134下限],
			[セシウム137下限],
			[コメント],
			[備考],
			[値ヨウ素131],
			[値セシウム134],
			[値セシウム137],
			[値セシウム合計],
			[値ヨウ素131下限],
			[値セシウム134下限],
			[値セシウム137下限],
			[値セシウム合計下限]
		FROM dbo.Web放射能結果
			WHERE 測定日>=@期間開始 and 測定日<(@期間終了+1) and 検体名 like @検体情報;
	END

	RETURN
END

のを作成して、動作確認をして上手く動作させることができました。

似たようなものをいくつか作成する必要があるのですが、項目名をいちいち書くのがとても大変です。

全項目を返すのでよいので、データを検索する部分を

		INSERT @returntable
		SELECT　*
		FROM dbo.Web放射能結果
			WHERE 測定日>=@期間開始 and 測定日<(@期間終了+1) and 検体名 like @検体情報;

のように記述したいのですが、そのとき、

RETURNS @returntable TABLE
(
    /*　この部分に項目名と型を列挙しないで済む方法は？　*/
)

この部分に項目名と型を列挙しないで済む方法は、ないでしょうか？

また、みなさんはどのように、間違えずに項目名と型の列挙を記載していますか？

一番神経と時間を使いながら、あまり生産性のよくない作業ですよね。

よろしくお願いします。

Answer 1

単純にテーブルの中身をSELECTして返しているだけに見えますので
インライン テーブル値関数を使えば良いのではないでしょうか。
関数の種類

ただし戻り値としては単数のSELECT文でないとダメなようなのでIFによる分岐はできません。
なので工夫してIF分岐をなくさないといけません。

CREATE FUNCTION dbo.期間放射能結果
(
	-- 引数省略
)
RETURNS TABLE  
AS 
RETURN
(
	SELECT *
	FROM dbo.Web放射能結果
		WHERE 測定日>=@期間開始 AND 測定日<(@期間終了+1) 
			AND 検体名 LIKE '%' + RTRIM(ISNULL(@検体情報, '')) + '%'
)

みたいな感じになるかと思います。
個人的にはめんどくさくても戻り値をちゃんと書く形のほうが良いと思っていますし、
今回の場合だと関数にする意味あるかなあ、とも思います。

Answer 2

また、みなさんはどのように、間違えずに項目名と型の列挙を記載していますか？

基本は手で打つことはないですね。今回の場合ですと、以下のSQLをSQL Server Management Studioで実行し、結果をコピーして貼り付ければＯＫだと思います。ただし、最後のカンマは手で削除して下さいね。

select '[' + COLUMN_NAME + '] ' + DATA_TYPE +
 　iif(CHARACTER_MAXIMUM_LENGTH is null, ',', '(' + convert(varchar, CHARACTER_MAXIMUM_LENGTH) + '),')
from INFORMATION_SCHEMA.COLUMNS
where 
     TABLE_NAME = 'テーブル名'
order by ORDINAL_POSITION

実際には、このようなツールをC#でいくつか作成し、運用しています。

---

★良い回答には回答済みマークを付けよう！ MVP - .NET 　http://d.hatena.ne.jp/trapemiya/

Answer 3

ふと思いついたのですが、SQL Server Mangement Studioで当該のテーブルを右クリックし、テーブルをスクリプト化　->　新規作成　->　新しいクエリエディタウインドウ　で、このテーブルのCREATE TABLE文が生成されますから、そこから必要な部分をコピーしても良いと思います。
ただし、NOT NULLやNULLの文字列も書き出されますから、それらは置換で長さ０の文字列に置き換えてしまう必要がありますし、データタイプも[]で括られてしまいますね。

---

★良い回答には回答済みマークを付けよう！ MVP - .NET 　http://d.hatena.ne.jp/trapemiya/

Answer 4

mars12さま

ありがとうございます。

インラインテーブル値関数ならば、項目名と型の列挙はいらないですね。最初の選択肢になります。

そして、以下の技も素敵でした。

AND 検体名 LIKE '%' + RTRIM(ISNULL(@検体情報, '')) + '%'

Answer 5

trapemiyaさま

ありがとうございました。求めていた答えのひとつです。活用させていただきます。

Answer 6

trapemiyaさま

ありがとうございました。この方法は、自分も気が付いていて実施例を作るときに利用しました。

しかし、いろいろと手作業で余分な情報を削除しなくてはならないので、困っていました。

ひとつ前で教えていただいたクエリが、大いに助かりました。