トップ回答者
ユーザー定義 テーブル値関数で、返すテーブル値の項目名と型の簡潔な記述方法は?

質問
-
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), [重量kg] 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], [検体名], [日付意味], [日付], [測定日], [測定機関], [重量kg], [測定分], [種別], [ヨウ素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], [検体名], [日付意味], [日付], [測定日], [測定機関], [重量kg], [測定分], [種別], [ヨウ素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 ( /* この部分に項目名と型を列挙しないで済む方法は? */ )
この部分に項目名と型を列挙しないで済む方法は、ないでしょうか?
また、みなさんはどのように、間違えずに項目名と型の列挙を記載していますか?
一番神経と時間を使いながら、あまり生産性のよくない作業ですよね。
よろしくお願いします。
回答
-
単純にテーブルの中身をSELECTして返しているだけに見えますので
インライン テーブル値関数を使えば良いのではないでしょうか。
関数の種類ただし戻り値としては単数のSELECT文でないとダメなようなのでIFによる分岐はできません。
なので工夫してIF分岐をなくさないといけません。
CREATE FUNCTION dbo.期間放射能結果 ( -- 引数省略 ) RETURNS TABLE AS RETURN ( SELECT * FROM dbo.Web放射能結果 WHERE 測定日>=@期間開始 AND 測定日<(@期間終了+1) AND 検体名 LIKE '%' + RTRIM(ISNULL(@検体情報, '')) + '%' )
みたいな感じになるかと思います。
個人的にはめんどくさくても戻り値をちゃんと書く形のほうが良いと思っていますし、
今回の場合だと関数にする意味あるかなあ、とも思います。- 回答としてマーク hiroshi.tsuchida 2016年6月28日 17:42
-
また、みなさんはどのように、間違えずに項目名と型の列挙を記載していますか?
基本は手で打つことはないですね。今回の場合ですと、以下のSQLをSQL Server Management Studioで実行し、結果をコピーして貼り付ければOKだと思います。ただし、最後のカンマは手で削除して下さいね。
select '[' + COLUMN_NAME + '] ' + DATA_TYPE +
実際には、このようなツールをC#でいくつか作成し、運用しています。
iif(CHARACTER_MAXIMUM_LENGTH is null, ',', '(' + convert(varchar, CHARACTER_MAXIMUM_LENGTH) + '),') from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'テーブル名' order by ORDINAL_POSITION
★良い回答には回答済みマークを付けよう! MVP - .NET http://d.hatena.ne.jp/trapemiya/
- 回答としてマーク hiroshi.tsuchida 2016年6月28日 17:37
すべての返信
-
単純にテーブルの中身をSELECTして返しているだけに見えますので
インライン テーブル値関数を使えば良いのではないでしょうか。
関数の種類ただし戻り値としては単数のSELECT文でないとダメなようなのでIFによる分岐はできません。
なので工夫してIF分岐をなくさないといけません。
CREATE FUNCTION dbo.期間放射能結果 ( -- 引数省略 ) RETURNS TABLE AS RETURN ( SELECT * FROM dbo.Web放射能結果 WHERE 測定日>=@期間開始 AND 測定日<(@期間終了+1) AND 検体名 LIKE '%' + RTRIM(ISNULL(@検体情報, '')) + '%' )
みたいな感じになるかと思います。
個人的にはめんどくさくても戻り値をちゃんと書く形のほうが良いと思っていますし、
今回の場合だと関数にする意味あるかなあ、とも思います。- 回答としてマーク hiroshi.tsuchida 2016年6月28日 17:42
-
また、みなさんはどのように、間違えずに項目名と型の列挙を記載していますか?
基本は手で打つことはないですね。今回の場合ですと、以下のSQLをSQL Server Management Studioで実行し、結果をコピーして貼り付ければOKだと思います。ただし、最後のカンマは手で削除して下さいね。
select '[' + COLUMN_NAME + '] ' + DATA_TYPE +
実際には、このようなツールをC#でいくつか作成し、運用しています。
iif(CHARACTER_MAXIMUM_LENGTH is null, ',', '(' + convert(varchar, CHARACTER_MAXIMUM_LENGTH) + '),') from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'テーブル名' order by ORDINAL_POSITION
★良い回答には回答済みマークを付けよう! MVP - .NET http://d.hatena.ne.jp/trapemiya/
- 回答としてマーク hiroshi.tsuchida 2016年6月28日 17:37
-
ふと思いついたのですが、SQL Server Mangement Studioで当該のテーブルを右クリックし、テーブルをスクリプト化 -> 新規作成 -> 新しいクエリエディタウインドウ で、このテーブルのCREATE TABLE文が生成されますから、そこから必要な部分をコピーしても良いと思います。
ただし、NOT NULLやNULLの文字列も書き出されますから、それらは置換で長さ0の文字列に置き換えてしまう必要がありますし、データタイプも[]で括られてしまいますね。
★良い回答には回答済みマークを付けよう! MVP - .NET http://d.hatena.ne.jp/trapemiya/
- 編集済み trapemiya 2016年6月28日 2:37
-
mars12さま
ありがとうございます。
インラインテーブル値関数ならば、項目名と型の列挙はいらないですね。最初の選択肢になります。
そして、以下の技も素敵でした。
AND 検体名 LIKE '%' + RTRIM(ISNULL(@検体情報, '')) + '%'
- 回答としてマーク hiroshi.tsuchida 2016年6月28日 17:37
- 回答としてマークされていない hiroshi.tsuchida 2016年6月28日 17:42