VB.NET-TIPS などプログラミングについて

当然といえばそうなのかもしれませんが、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などを行いたい場合、 全てのファンクションをプロシージャにしないといけなくなってきます。
このあたりは、ファンクション、プロシージャの切り分けをしっかりしておく必要がありそうです。
=====
2013/02/14：の時の情報