ユーザ定義関数（スカラ値関数）の作り方（SQL Server 2005）

job

具体例が意外に少なくて初心者には大変悲しかったので、簡単なサンプルを残しときます。
色んな権限はもちろん持っているという前提で。

  • スキーマ
    • wkwk
  • テーブル
    • tblBuilding（ビル情報格納テーブル）
      • BuildingCode:ビルコード
      • AreaCode:地域コード


    • tblChange（変更情報格納テーブル）
      • BuildingCode:ビルコード
      • SeqNo:ビルコード毎の連番
      • ChangeDate:変更日
      • ChangeColumn:変更した項目（1:地域コード）
      • ValueBefore:変更前の値
      • ValueAfter:変更後の値




  • sys.objectsのtype
    • FN = SQL スカラ関数

詳しくは以下を参照してください。
http://msdn2.microsoft.com/ja-jp/library/ms190324.aspx


ビルの地域コードをちみちみ変更するのですが、例えば去年の12月末時点の地域コードが何だったか知りたいわ、というときに使う関数です。
ビルコードと日付を指定すると地域コードが返ります。
あくまでもサンプルなので、地域コードがちみちみ変わるなんて！とかそういうツッコミはいやんです。
こんな感じです。



SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/* 存在する場合は削除 */

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'wkwk.GetBuildingArea') AND type in (N'FN'))

  DROP FUNCTION wkwk.GetBuildingArea;

GO
CREATE FUNCTION wkwk.GetBuildingArea (@BuildingCode int, @TargetDate smalldatetime)

RETURNS smallint

AS

BEGIN

  DECLARE @intRtn smallint

  DECLARE @intValueNow smallint

  DECLARE @intValueBefore smallint
  SET @intValueNow = 

    (SELECT AreaCode

      FROM wkwk.tblBuilding

      WHERE

        BuildingCode = @BuildingCode

    )

  SET @intRtn = @intValueNow
  /* 変更している場合は、指定日直後の変更前の値を採用 */

  SET @intValueBefore = 

    (SELECT TOP 1 ValueBefore

      FROM wkwk.tblChange

      WHERE

        BuildingCode = @BuildingCode

        AND ChangeDate > @TargetDate

        AND ChangeColumn = 1

      ORDER BY

        ChangeDate ASC,

        SeqNo ASC

    )
  IF @intValueBefore IS NOT NULL

    SET @intRtn = @intValueBefore
  RETURN @intRtn

END;

GO