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

  • 質問

  • 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
    (
        /* この部分に項目名と型を列挙しないで済む方法は? */
    )

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

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

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

    よろしくお願いします。

    2016年6月27日 8:45

回答

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

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

    CREATE FUNCTION dbo.期間放射能結果
    (
    	-- 引数省略
    )
    RETURNS TABLE  
    AS 
    RETURN
    (
    	SELECT *
    	FROM dbo.Web放射能結果
    		WHERE 測定日>=@期間開始 AND 測定日<(@期間終了+1) 
    			AND 検体名 LIKE '%' + RTRIM(ISNULL(@検体情報, '')) + '%'
    )
    みたいな感じになるかと思います。
    個人的にはめんどくさくても戻り値をちゃんと書く形のほうが良いと思っていますし、
    今回の場合だと関数にする意味あるかなあ、とも思います。

    2016年6月28日 0:55
  • また、みなさんはどのように、間違えずに項目名と型の列挙を記載していますか?

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

    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/

    2016年6月28日 2:08

すべての返信

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

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

    CREATE FUNCTION dbo.期間放射能結果
    (
    	-- 引数省略
    )
    RETURNS TABLE  
    AS 
    RETURN
    (
    	SELECT *
    	FROM dbo.Web放射能結果
    		WHERE 測定日>=@期間開始 AND 測定日<(@期間終了+1) 
    			AND 検体名 LIKE '%' + RTRIM(ISNULL(@検体情報, '')) + '%'
    )
    みたいな感じになるかと思います。
    個人的にはめんどくさくても戻り値をちゃんと書く形のほうが良いと思っていますし、
    今回の場合だと関数にする意味あるかなあ、とも思います。

    2016年6月28日 0:55
  • また、みなさんはどのように、間違えずに項目名と型の列挙を記載していますか?

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

    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/

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

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

    • 編集済み trapemiya 2016年6月28日 2:37
    2016年6月28日 2:37
  • mars12さま

    ありがとうございます。

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

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

    AND 検体名 LIKE '%' + RTRIM(ISNULL(@検体情報, '')) + '%'
    2016年6月28日 17:34
  • trapemiyaさま

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

    2016年6月28日 17:37
  • trapemiyaさま

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

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

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

    2016年6月28日 17:42