ユーザー定義関数内でのsp_executesqlプロシージャの使用

質問

SQLServer2005を使用しています。
今、ユーザー定義関数を作成していて、クエリアナライザにて動作を検証しています。

ユーザー定義関数内で、sp_executesqlを実行しようとすると、
「サーバー : メッセージ 557、レベル 16、状態 2、プロシージャ F_GET_DAY、行 26　関数内から実行できるのは関数と拡張ストアド プロシージャだけです。」
というエラーが出ます。

sp_executesqlは、マスターデータベースの拡張プロシージャに位置づけられているので、実行可能だと思ったのですが・・・。
同じく、マスターデータベースの拡張プロシージャにである「sp_oacreate」の実行を試してみると、関数内で実行できました。
sp_executesqlは、関数内では実行できないのでしょうか。

回答No.3

tasokarewa · ベストアンサー

ANO.1です。
＞＞スカラ値関数ではOKのようです（補足で頂いたSQLもOKです）。
＞ここのところを再度ご教授願えないでしょうか。

なるほど納得です。私はEXECUTEしてました。

例：
DECLARE @ret int
EXEC @ret = dbo.F_GET_DAY

通常、関数は、SELECTでも使用しますので、
そのあたり検証すべきでした。
申し訳ありません。

SELECT, PRINTともＮＧで、EXECUTE はＯＫでした。

拡張ストアドの種類や関数の実行の仕方など、
SQLServerが判断して実行可否を決定しているのですね。
で、その判断基準は明記されておらず
（関数の設計基準はありますが）、
実行してみないことにはわからないようです。

またまた勉強になりました。

お礼

ご回答、ありがとうございます！

＞SQLServerが判断して実行可否を決定しているのですね。
＞で、その判断基準は明記されておらず
＞（関数の設計基準はありますが）、
＞実行してみないことにはわからないようです。

エラーメッセージでは可能なような気がしてしまったので、どうなのかな、と思いましたが、SELECT、PRINTの場合はやはりNGなのですね、、、
しかし、この辺の判断基準は明確にしていただきたいものですね。

ありがとうございます、ファンクションの奥深さを教えていただきました☆

回答No.2

tasokarewa

ANo.1です。
私の理解不足でした。現象が確認できました。
テーブル値関数内で、「sp_executesql」を実行すると、
「関数内から実行できるのは・・」エラーが発生しました。
作成されているユーザー関数はテーブル値関数ではないですか？
スカラ値関数ではOKのようです（補足で頂いたSQLもOKです）。
ヘルプには関数内で「sp_executesql」は実行可能であるかのように
書かれております。
テーブル値関数で、ＮＧなのかは、明記されておりませんでした。
（ストアドでSELECTの結果を返すときに、
ストアド内で「EXECUTE」を利用していない場合は、
関数に置き換え可能であるとの記述は見つけましたが。）

おそらく関数とは、
１．スカラ関数は値を返す。
２．テーブル値関数はビューでは実現できない形で結果を得る、
　すなわち、パラメータを渡せたり、テーブル変数に対して
　更新などの処理を行い、結果を返す。

もので、それ以上のことを行うときは、
エラーが発生するかもしれないので、
ストアドプロシージャを利用しましょう
ということになるのでしょうか。

勉強になりました。

補足

ご回答ありがとうございます！
なるほど、テーブル値関数ではNGなのですね、ヘルプではそこまで読み取れないとのこと、大変貴重なご意見いただきました。ありがとうございます。

しかし、私の場合はそれ以前の問題のようです、、、
私が今回使用しているのはスカラ値関数です。

1.ファンクション作成
CREATE FUNCTION F_test ()
RETURNS int
AS
BEGIN
DECLARE @SQL NVARCHAR(4000)

--SQL作成
SET @SQL = N'INSERT INTO T_CAL VALUES (''2006/12/04'',1)'
--実行
EXECUTE sp_executesql @SQL
--戻り値
RETURN (1)
END

2.クエリアナライザより、実行
select dbo.F_test()

とした場合、「サーバー : メッセージ 557、レベル 16、状態 2、プロシージャ F_test、行 10
関数内から実行できるのは関数と拡張ストアド プロシージャだけです。」
というエラーが発生します。

＞スカラ値関数ではOKのようです（補足で頂いたSQLもOKです）。
ここのところを再度ご教授願えないでしょうか。

回答No.1

tasokarewa

関数内からsp_executesqlは実行できます。
sp_executesqlに渡している文字列内に実行できないコマンドがあるとかではないですか？
ログインユーザーの権限とsp_executesqlに渡している文字列の内容を可能な限り教えて頂けますか？

補足

tasokarewa様
ご意見ありがとうございます！

１．sp_executesqlに渡している文字列の内容は、テスト的に簡単なINSERT文としています。

SET @SQL = N'INSERT INTO T_CAL VALUES (''2006/12/04'',1)'
EXECUTE sp_executesql @SQL

上記は、プロシージャでは実行可能ですが、ファンクションではエラーがでます。

２．ログインユーザの権限というところは怪しいのですが、、、
・クエリアナライザでは、「おそらく全てのデータベースに接続できる」と思っているsaユーザにて接続しています。
・実行可能なプロシージャ、実行不可能なファンクションとも、所有者dboで作成されています。

・・・補足回答になっているでしょうか、、、

関数内からsp_executesqlを実行している例がネット上、どうしても見つかりません。（何れもストアドプロシージャか無名プロシージャです。。。）
できれば、実行可能なファンクションサンプルを教えていただけないでしょうか。