月: 2009年12月

投稿日:

SQL Server ユーザー定義関数(3) スカラー値関数

ユーザー定義関数 その3

— =============================================
— スカラー値関数 (データーを1つだけ返す)
— =============================================
CREATE FUNCTION fn_Test3
(
    @a1 int
)
RETURNS int
AS
BEGIN
    DECLARE @Result int

    SELECT @Result = b1 FROM Table1 WHERE ID = @a1

    RETURN @Result

END
GO

使い方

Declare @b1 int

1．パラメーター名指定 (順不同)

    EXEC @b1 = fn_Test3 @a1 = 1

2．パラメーター名を指定しない (順序は定義に合わせる)

    EXEC @b1 = fn_Test3 1

3．クエリーで使用

    SELECT c1, dbo.fn_Test3(ID) AS col1
        FROM Table1

    なぜか
   
SELECT c1, fn_Test3(ID) AS col1
        FROM Table1
   
ではダメでスキーマ名を指定する必要がある．

参考：
ユーザー定義関数 (MSDN)

投稿日:

SQL Server ユーザー定義関数(2) 複数ステートメントのテーブル値関数

ユーザー定義関数 その2

— =============================================
— 複数ステートメントのテーブル値関数
— =============================================
CREATE FUNCTION fn_Test2
(
    @a1 int
)
RETURNS @retT TABLE
(
       — テーブル値の定義
        ID int primary key NOT NULL
    ,    b1 nvarchar(250)
)
AS
BEGIN

    WITH T1(ID1, Name1) AS
    (
        SELECT ID1, Name1 FROM Table1 WHERE ID2 = @a1
        UNION ALL   
        SELECT ID1, Name1 FROM Table2 WHERE ID3 = @a1
    )

    — @retT にデーターを入れて返す
    INSERT INTO @retT
        SELECT ID1_ID, Name1 FROM T1

    RETURN

END
GO

使い方

SELECT * FROM fn_Test2(1) –カッコの中にパラメーター値を入れる

参考：
ユーザー定義関数 (MSDN)

投稿日:

SQL Server ユーザー定義関数(1) インラインテーブル値関数

ユーザー定義関数 その1

— =============================================
— インラインテーブル値関数
— RETURN句のカッコ内にはSELECT文を1つだけ書ける
— 注意：SELECT * を使うと，その後テーブルに追加された列の情報は返らない．
— (ALTERで関数を定義し直せば大丈夫だが，*は使わない方がいいだろう．)
— =============================================
CREATE FUNCTION fn_Test1
(   
    @a1 int
)
RETURNS TABLE
AS
RETURN
(
    SELECT b1, b2
        FROM Table1
        WHERE ID = @a1
)
GO

使い方

SELECT * FROM fn_Test1(1) –カッコの中にパラメーター値を入れる

次のようにUPDATE文を使うと，元のTable1の内容が書き換わる．
間違ってそのままテーブル変数や一時テーブルのように使うと痛い目を見る．

UPDATE fn_Test1(1) SET b1 = 1

参考：
ユーザー定義関数 (MSDN)