「副作用のある演算子を関数内で使用するのは無効」を回避することはできない？

前回からの続きです。CLF 日付書式を日付型に変換する以下のような関数を作成すると以下のように「副作用のある演算子」とやらを使ってるのがダメってなエラーがでました。

CREATE FUNCTION TO_DATETIME(@dtstr VARCHAR(max) )
RETURNS DATETIME
AS
BEGIN
  SET LANGUAGE 'British English'
  RETURN TRY_CONVERT(DATETIME,STUFF(@dtstr, CHARINDEX(':',@dtstr),1,' '))
END
メッセージ 443、レベル 16、状態 15、プロシージャ TO_DATETIME、行 5 [バッチ開始行 0]
副作用のある演算子 'SET COMMAND' を関数内で使用するのは無効です。

この「副作用のある演算子」はデータベースの状態を変える可能性がある、という意味っぽいですが、正直意味がよくわかりません。世の中のみなさん苦労されているみたいで、TRY CATCH や PRINT 等でも発生します。
なんだか悔しいので、今回はこれを回避した上で意地でも関数を作成することに挑戦してみます。

関数内からストアドプロシジャの呼び出し

まず、関数で SET を利用することは制約のようなので、上記をプロシジャで実装することにします。
SQLServer のプロシジャから値を戻す方法には３種類ありますが、この中でも「出力パラメータを使用してデータを返す」で作成します。

CREATE PROCEDURE CONVERT_TO_DATETIME (@dtstr VARCHAR(max), @dt DATETIME OUTPUT )
AS
BEGIN
  SET LANGUAGE 'British English'
  SET @dt = TRY_CONVERT(DATETIME,STUFF(@dtstr, CHARINDEX(':',@dtstr),1,' '))
  RETURN
END

とりあえず実行してみます。

DECLARE @dt DATETIME
EXEC dbo.CONVERT_TO_DATETIME '02/Sep/2021:14:32:19',@dt OUTPUT
PRINT 'dt:[' + format(@dt,'yyyy/MM/dd HH:mm:ss') + ']'
ストアドプロシジャでの実行（その１）

うまく動いているようです。では、このプロシジャを呼び出す関数を作れば、、、と安直に考えてみます。

CREATE FUNCTION TO_DATETIME(@dtstr VARCHAR(max) )
RETURNS DATETIME
AS
BEGIN
  DECLARE @dt DATETIME
  EXEC dbo.CONVERT_TO_DATETIME @dtstr,@dt OUTPUT
  RETURN @dt
END

コンパイルは通って関数登録できました。呼び出してみましょう。

メッセージ 557、レベル 16、状態 2、行 1
関数内から実行できるのは関数と一部の拡張ストアド プロシージャだけです。

これも副作用云々の制約なのでしょうか。でもこれって関数から自分で作ったプロシジャが呼び出せないってことですよね。普通に不便な気がします。「EXEC」を使ったのがいけないのでしょうか。

OPENROWSET経由でのストアドプロシジャ呼び出し

EXEC 以外でプロシジャを呼び出す方法として、OPENROWSET を使って結果をテーブル値として取得する方法があります。OPENROWSET はこちらの記事でも紹介していますが、とにかく色々な方法で取得したデータをテーブル値として取得する関係演算子です。
まず日付変換のプロシジャは「出力パラメータを使用してデータを返す」形式から「結果セットを使用してデータを返す」形式に変更します。

CREATE PROCEDURE CONVERT_TO_DATETIME (@dtstr VARCHAR(max) )
AS
BEGIN
  SET LANGUAGE 'British English'
  SELECT TRY_CONVERT(DATETIME,STUFF(@dtstr, CHARINDEX(':',@dtstr),1,' ')) AS DT
END

実行してみます。

EXEC dbo.CONVERT_TO_DATETIME '02/Sep/2021:14:32:19'

ストアドプロシジャでの実行（その２）

結果セットとして返ってきています。それでは、このプロシジャを OPENROWSET を介して呼び出してみましょう。

SELECT T.DT
  FROM OPENROWSET(
     'SQLOLEDB'
   , 'Data Source=;Trusted_connection=yes;'
   , 'EXEC dbo.CONVERT_TO_DATETIME ''02/Sep/2021:14:32:19'''
 ) T;
ストアドプロシジャをOPENROWSET経由で実行

これを関数から実行すれば、少なくとも関数内から EXEC でプロシジャ実行しているわけではないので制約にはかからないように思えます。とりあえずは、まんま埋め込んだ関数を作って実行してみます。

CREATE FUNCTION TO_DATETIME(@dtstr VARCHAR(max) )
RETURNS DATETIME
AS
BEGIN
  DECLARE @dt DATETIME
  SELECT @dt = T.DT
    FROM OPENROWSET(
       'SQLOLEDB'
     , 'Data Source=;Trusted_connection=yes;'
     , 'EXEC dbo.CONVERT_TO_DATETIME ''02/Sep/2021:14:32:19'''
    ) T;
  RETURN @dt
END
ストアドプロシジャを関数から実行（その２）

うまくいきましたね。
じゃあ、あとはプロシジャ実行時のパラメータを変えるだけ、、、、のはずが、、、、

コンパイルエラーとなりました。どうやら OPENROWSET のパラメータに変数は使えないようです、、、orz

OPENROWSET の引数に変数は指定できません。

OPENROWSET (Transact-SQL)

動的SQLによるOPENROWSETの呼び出し

すでに手詰まり感満載ですが、OPENROWSETに変数を渡せないことを回避するためには全体を文字列にして動的SQLにするしか方法はないみたいです。ただ、既にお察しのとおり、単に SET が副作用があるといってるのに動的SQLなぞ動くとは思えません。結論から書きますと結果的にこの方法では関数化はできませんでした。

sp_executesql を利用した動的SQL

sp_executesql は拡張ストアドプロシジャだと思うので望みがありそうでしょうか、、、

  DECLARE @dtstr VARCHAR(max) = '02/Sep/2021:14:32:19'

  DECLARE @dt DATETIME
  DECLARE @param NVARCHAR(max) = N'@dtOut DATETIME OUTPUT';  
  DECLARE @query NVARCHAR(max) = N''
  SET @query = @query + 'SELECT @dtOut = T.DT FROM OPENROWSET(''SQLOLEDB'',''Data Source=;Trusted_connection=yes;'',''EXEC dbo.CONVERT_TO_DATETIME '''''+@dtstr+''''''') T'

  EXEC sp_executesql @query, @param, @dtOut = @dt OUTPUT

  PRINT 'dt : [' + format(@dt,'yyyy/MM/dd HH:mm:ss') + ']'

上記はそのまま動作しますが、これを関数内に埋め込んで （ただし PRINT は除く） 実行した結果は以下です。
あら。sp_executesql は「一部の拡張ストアドプロシージャ」ではないのね、、、、orz

関数内から実行できるのは関数と一部の拡張ストアド プロシージャだけです。

EXEC でカーソルを使った動的SQL

EXEC そのものはエラーではないのでしょうか。では動的カーソルを使ったSQLの実行はどうでしょう。

  DECLARE @dtstr VARCHAR(max) = '02/Sep/2021:14:32:19'

  DECLARE @dt DATETIME
  DECLARE @query NVARCHAR(max) = N'DECLARE cur CURSOR FOR ';  
  SET @query = @query + N'SELECT * FROM OPENROWSET(''SQLOLEDB'',''Data Source=;Trusted_connection=yes;'',''EXEC dbo.CONVERT_TO_DATETIME '''''+@dtstr+''''''') T'

  EXEC(@query)

  OPEN cur
  FETCH NEXT FROM cur INTO @dt

  PRINT 'dt : [' + format(@dt,'yyyy/MM/dd HH:mm:ss') + ']'

  CLOSE cur
  DEALLOCATE cur

こちらを関数内に埋め込んでみたところ（ただし PRINT 関数は削除しました）見事にコンパイルエラーです。
そうですか。そうですよね。申し訳ございません、、、

メッセージ 443、レベル 16、状態 14、プロシージャ TO_DATETIME、行 9 [バッチ開始行 0]
副作用のある演算子 'EXECUTE STRING' を関数内で使用するのは無効です。

結局、冒頭のエラー「副作用のある演算子～」を回避することはできないので、やっぱりそれを使わない方法で回避してね、ってことですね。ただし、そのプロシジャに変数を渡す場合に限りますので、今回の検証が全く無駄だったということではなさそうです。つまり「変数を渡す必要のないプロシジャであれば、OPENROWSET 経由で関数から呼び出せる」ということですね。（どれだけ用途があるかは未知数ですが、、、）

参考

OPENROWSET 実行時のエラー

OPENROWSET を使ったストアドプロシジャプロシジャの実行ですが、SQLServerインストール後のデフォルト値のままで実行した場合、以下のエラーが発生します。

メッセージ 15281、レベル 16、状態 1、行 1
SQL Server によって、コンポーネント 'Ad Hoc Distributed Queries' の STATEMENT 'OpenRowset/OpenDatasource' に対するアクセスがブロックされました。このサーバーのセキュリティ構成で、このコンポーネントが OFF に設定されているためです。システム管理者は sp_configure を使用して、'Ad Hoc Distributed Queries' の使用を有効にできます。'Ad Hoc Distributed Queries' を有効にする手順の詳細については、SQL Server オンライン ブックで、'Ad Hoc Distributed Queries' を検索してください。

検索して出てくるMSのページは「ad hoc distributed queries サーバー構成オプション」です。
ここにあるオプション変更（sp_configure）を実行した上で再実行すれば大丈夫です。
ただし、使用上の注意はよく読んでご利用ください、、、ね。

アドホック分散クエリの実行時には、OLE DB を使用するリモート データ ソースへの接続に OPENROWSET 関数および OPENDATASOURCE 関数が使用されます。 OPENROWSET 関数および OPENDATASOURCE 関数は、アクセス頻度の低い OLE DB データ ソースを参照する目的のみに使用してください。 何度もアクセスするデータ ソースに対しては、リンク サーバーを定義してください。

ad hoc distributed queries サーバー構成オプション

OPENROWSET に指定できるプロバイダ

OPENROWSET に与えられるプロバイダについては、SSMS(SQL Server Management Studio)のリンクサーバー配下のプロバイダーを参照すればわかります。今回の例では SQLOLEDB を使っていますが、 SQLNCLI 等も利用できます。

SQLServerにアクセスするためのドライバーについては「Microsoft SQL Server のドライバー履歴」に説明がありました。

データベースSQLServer,T-SQL

Posted by takmaru