none
テーブル値関数からストアドプロシージャを呼び出したいのですが RRS feed

  • 質問

  • お世話になります.環境はWin7, SQL Server 2008 R2です.

    あるレコードの2つの属性から作出した項目がカットオフ値以下かそうでないかで四文表を作成するストアドプロシージャを作成しました.

    カットオフ値を変数化して年ごとの四文表の要素,年,カットオフ値を複数行返します.

    そのプロシージャをテーブル値関数で呼び出したいのですが,構文エラーのようで関数の作成ができません.

    『メッセージ 156、レベル 15、状態 1、プロシージャ fnc_2DimensionTable、行 12

    キーワード 'AS' 付近に不適切な構文があります。』

    ALTER PROCEDURE [dbo].[sp_TwoByTwoTable]
    		@CutOff	decimal(5, 2)
    AS
    BEGIN
    SELECT	@CutOff	
    	,	T.[YEAR]
    	,	T.N
    	,	T.[TruePositive(a)]
    	,	T.[FalsePositive(b)]
    	,	T.[FalseNegative(c)]
    	,	T.[TrueNegative(d)]
    	,	T.[Positive(a+b)]
    	,	T.[Negative(c+d)]
    	,	T.[TRUE(a+c)]
    	,	T.[FALSE(b+d)]
    	
    FROM	TwoTwoTable AS T
    END
    
    CREATE FUNCTION fnc_2DimensionTable 
    	(	@Start	decimal(5, 2)
    	,	@End	decimal(5, 2)
    	,	@Step	decimal(5, 2)
    	)
    RETURNS @ResultTable TABLE 
    AS
    BEGIN
    	DECLARE	@CutOff decimal(5, 2)
    	SET @CutOff = @Start
    	WHILE @CutOff < @End 
    	BEGIN;
    		INSERT INTO @ResultTable EXEC sp_TwoByTwoTable @cutoff 
    		SET @CutOff = @CutOff + @Step 
    	END;
    END
    RETURN 
    
    GO
    

    関数でやりたいことは,開始値から順にステップ値を加算していって最終値に達したら終了とするものです.

    どこが良くないのでしょうか,よろしくお願いいたします.

    2012年11月1日 21:10

回答

  • 関数内では副作用を伴う実行は禁止されていますので、そのエラーが出るのは仕様でしょう。以下のページにも、ストアドプロシージャに関して実行できるものは、「拡張ストアド プロシージャを呼び出す EXECUTE ステートメント。」としか、書かれていません。

    ユーザー定義関数の作成 (データベース エンジン)
    http://msdn.microsoft.com/ja-jp/library/ms191320(v=sql.105).aspx

    よって、違う方法を考える必要があると思います。全体の仕様がわからないので何とも言えないのですが、例えば、関数の中から関数を呼べますので、それを上手に使えばうまく行くかもしれません。あと、CTE(共通テーブル式)や一時テーブルなども使えるかもしれません。


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

    • 回答としてマーク じふ 2012年11月2日 3:54
    2012年11月2日 3:08

すべての返信

  • @ResultTable のカラムの定義が無いからだと思います。また、最後のRETURNの位置も誤りです。以下に例が載っていますので、参考にしてみて下さい。

    ユーザー定義テーブル値関数
    http://msdn.microsoft.com/ja-jp/library/ms191165(v=sql.105).aspx


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

    2012年11月2日 0:47
  • ありがとうございます.

    テーブル定義を追加してみたところ,AS付近でのエラーは出なくなったのですが,今度は別のエラーが出てきました.

    CREATE FUNCTION fnc_2DimensionTable 
    	(	@Start	decimal(5, 2)
    	,	@End	decimal(5, 2)
    	,	@Step	decimal(5, 2)
    	)
    RETURNS @ResultTable TABLE 
    	(	CutOff	decimal(5, 2)	NOT NULL
    	,	[YEAR]	int	NOT NULL
    	,	N	int	NOT NULL
    	,	a	int	NOT NULL
    	,	b	int	NOT NULL
    	,	c	int	NOT NULL
    	,	d	int	NOT NULL
    	,	[a+b]	int	NOT NULL
    	,	[c+d]	int	NOT NULL
    	,	[a+c]	int	NOT NULL
    	,	[b+d]	int	NOT NULL
    	,	Sensitivity	real	NOT NULL
    	,	Specificity	real	NOT NULL
    	,	FalsePositive	real	NOT NULL
    	,	[LR+]	real	NOT NULL
    	,	[LR-]	real	NOT NULL
    	,	[chi-square]	real	NOT NULL
    	,	[chi-square-Yates]	real	NOT NULL	
    	)
    AS
    BEGIN
    	DECLARE	@CutOff decimal(5, 2)
    	SET @CutOff = @Start
    	WHILE @CutOff < @End 
    	BEGIN;
    		INSERT INTO @ResultTable EXEC sp_TwoByTwoTable @cutoff 
    
    		SET @CutOff = @CutOff + @Step 
    	END;
    RETURN 
    END
    
    GO
    

    副作用のある演算子 'INSERT EXEC' を関数内で使用することはできません,と出ます.

    大変申し訳ありませんが,よろしくお願いします.

    2012年11月2日 1:57
  • 関数内では副作用を伴う実行は禁止されていますので、そのエラーが出るのは仕様でしょう。以下のページにも、ストアドプロシージャに関して実行できるものは、「拡張ストアド プロシージャを呼び出す EXECUTE ステートメント。」としか、書かれていません。

    ユーザー定義関数の作成 (データベース エンジン)
    http://msdn.microsoft.com/ja-jp/library/ms191320(v=sql.105).aspx

    よって、違う方法を考える必要があると思います。全体の仕様がわからないので何とも言えないのですが、例えば、関数の中から関数を呼べますので、それを上手に使えばうまく行くかもしれません。あと、CTE(共通テーブル式)や一時テーブルなども使えるかもしれません。


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

    • 回答としてマーク じふ 2012年11月2日 3:54
    2012年11月2日 3:08
  • お世話になります.

    下記コードでうまく結果セットを得られました.ありがとうございました.

    CREATE PROCEDURE sp_2DimensionTable
    	(	@Start	decimal(5, 2)
    	,	@End	decimal(5, 2)
    	,	@Step	decimal(5, 2)
    	)
    AS
    BEGIN
    	CREATE TABLE	#Result	
    	(	[CutOff]	decimal(5, 2)	NOT NULL
    	,	[YEAR]	int	NOT NULL
    	,	N	int	NOT NULL
    	,	a	int	NOT NULL
    	,	b	int	NOT NULL
    	,	c	int	NOT NULL
    	,	d	int	NOT NULL
    	,	[a+b]	int	NOT NULL
    	,	[c+d]	int	NOT NULL
    	,	[a+c]	int	NOT NULL
    	,	[b+d]	int	NOT NULL
    	,	Sensitivity	real	NOT NULL
    	,	Specificity	real	NOT NULL
    	,	FalsePositive	real	NOT NULL
    	,	[LR+]	real	NOT NULL
    	,	[LR-]	real	NOT NULL
    	,	[chi-square]	real	NOT NULL
    	,	[chi-square-Yates]	real	NOT NULL
    	)
    	DECLARE	@CutOff	decimal(5, 2)
    	SET @CutOff = @Start
    	WHILE @CutOff < @End 
    	BEGIN
    	INSERT INTO #Result EXEC sp_TwoByTwoTable @CutOff
    	SET @CutOff = @CutOff + @Step
    	END;
    	SELECT * FROM #Result
    END
    GO
    

    2012年11月2日 3:53