おやじプログラマー日記 : SQL-Serverのストアド・ファンクションではUPDATEなどが実行できない

当然といえばそうなのかもしれませんが、SQL-Serverのストアド・ファンクションではUPDATEなどが実行できない。
TRY・・・CATCHも記述できないようです。関数は戻り値のみを返すもので、テーブル等に変更を与えてはいけないのでしょう。

さて、取敢えずストアド・ファンクションの簡単な例を示します。

前回使用したテストテーブルを利用して、ストアド・ファンクションを作成します。テーブルの構造は以下の感じです。 CREATE TABLE [dbo].[TABLE_A]( [ID] [int] NOT NULL, [DATA1] [nvarchar](50) COLLATE Japanese_CI_AS NULL, [DATA2] [nvarchar](50) COLLATE Japanese_CI_AS NULL, CONSTRAINT [PK_TABLE_A] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
■スカラ値関数

テストテーブルＡを利用した簡単な値を返すストアド・関数です。
与えられた検索用ＩＤでテーブルＡの「DATA1」カラムの内容を返す簡単な関数です。 IF OBJECT_ID('dbo.FuncTest2') IS NOT NULL DROP FUNCTION [dbo].[FuncTest2] GO CREATE FUNCTION [dbo].[FuncTest2]( @SrcID NVARCHAR ) RETURNS NVARCHAR(50) BEGIN DECLARE @Des NVARCHAR(50) SELECT @Des = DATA1 FROM TABLE_A WHERE ID = @SrcID RETURN @Des END SELECT文での実行は以下のようにします。
======================================
SELECT [dbo].[FuncTest2](1)
======================================

■テーブル値関数その１（インラインテーブル値）

テストテーブルＡを利用した簡単な値を返すストアド・関数です。 IF OBJECT_ID('dbo.FuncTest3') IS NOT NULL DROP FUNCTION [dbo].[FuncTest3] GO CREATE FUNCTION [dbo].[FuncTest3]( @SrcID NVARCHAR ) RETURNS TABLE AS RETURN ( SELECT ID, DATA1 FROM TABLE_A WHERE ID = @SrcID )
SELECT文のFROM句で実行しますので、以下のようになります。
======================================
SELECT * FROM [dbo].[FuncTest3](1)
======================================

■テーブル値関数その２（複数の行を返すテーブル値）

テストテーブルＡを利用した複数の行を返すストアド・関数です。
指定されたID以降のデータを全て返す様な動作を行います。
RETURNS句で指定されたTABLEにINSERTしていくことで、結果をテーブルの値として返します。
IF OBJECT_ID('dbo.FuncTest4') IS NOT NULL DROP FUNCTION [dbo].[FuncTest4] GO CREATE FUNCTION [dbo].[FuncTest4]( @SrcID NVARCHAR ) RETURNS @DesTbl TABLE ( ID int primary key NOT NULL, DATA1 nvarchar(255) NOT NULL ) AS BEGIN INSERT @DesTbl SELECT ID, LTRIM( DATA1 ) FROM TABLE_A WHERE ID >= @SrcID /* INSERT @DesTbl VALUES(0, '0000') */ RETURN END INSERTで追加してやればいろんなことができると思います。上の例ではコメント行になっていますが、強制的にID「０」を作成することも可能です。
この関数の実行も「テーブル値関数その１」で行った方法と同様です。

■スカラ値関数にUPDATE文を記述
IF OBJECT_ID('dbo.FuncTest2') IS NOT NULL DROP FUNCTION [dbo].[FuncTest2] GO CREATE FUNCTION [dbo].[FuncTest2]( @SrcID NVARCHAR ) RETURNS NVARCHAR(50) BEGIN DECLARE @Des NVARCHAR(50) SELECT @Des = DATA1 FROM TABLE_A WHERE ID = @SrcID UPDATE TABLE_A SET DATA2 = NULL WHERE ID = @SrcID RETURN @Des END SQL実行結果として以下のエラーメッセージが表示されます。
============================================================
メッセージ 443、レベル 16、状態 15、プロシージャ FuncTest2、行 8
副作用のある演算子または時間に依存する演算子を関数内の 'UPDATE' で使用することはできません。
============================================================

TRY、CATCHを入れてもエラーが表示され使用出来ないようです。
IF OBJECT_ID('dbo.FuncTest2') IS NOT NULL DROP FUNCTION [dbo].[FuncTest2] GO CREATE FUNCTION [dbo].[FuncTest2]( @SrcID NVARCHAR ) RETURNS NVARCHAR(50) BEGIN BEGIN TRY DECLARE @Des NVARCHAR(50) SELECT @Des = DATA1 FROM TABLE_A WHERE ID = @SrcID END TRY BEGIN CATCH END CATCH RETURN @Des END SQL実行結果として以下のエラーメッセージが表示されます。
============================================================
メッセージ 443、レベル 16、状態 14、プロシージャ FuncTest2、行 6
副作用のある演算子または時間に依存する演算子を関数内の 'BEGIN TRY' で使用することはできません。
メッセージ 443、レベル 16、状態 14、プロシージャ FuncTest2、行 9
副作用のある演算子または時間に依存する演算子を関数内の 'END TRY' で使用することはできません。
メッセージ 443、レベル 16、状態 14、プロシージャ FuncTest2、行 10
副作用のある演算子または時間に依存する演算子を関数内の 'BEGIN CATCH' で使用することはできません。
メッセージ 443、レベル 16、状態 14、プロシージャ FuncTest2、行 11
副作用のある演算子または時間に依存する演算子を関数内の 'END CATCH' で使用することはできません。
============================================================

副作用のある処理をファンクションのなかでは記述できないので、どうしても処理したいのであれば、ストアド・プロシージャとして作成するしかないようです。
そこで、結果として何かを返したいのであれば、引数に出力属性のものを持つしかありません。

また、プロシージャの中からファンクションを実行することはできますが、ファンクションの中からプロシージャを実行することはできないようです。
ファンクションのコールが入れ子になっていて、深いところでUPDATEなどを行いたい場合、全てのファンクションをプロシージャにしないといけなくなってきます。
このあたりは、ファンクション、プロシージャの切り分けをしっかりしておく必要がありそうです。