[@IT]ユーザー定義関数から正しくない答えが返される
SQLServerでは、テーブル型を返すユーザー定義関数をインラインユーザー定義関数と呼ぶそうです。
例えば、以下のようなテーブルを作り、データを格納します。
(環境：SQL Server 2005 Express Edition)
[テーブル]
member_table
member_code int
member_name nvarchar(50)
[データ]
member_code member_name
1 中博俊
2 じゃんぬねっと
3 nakaP
member_codeを指定して、該当する行を返すユーザー定義関数を作ります。
CREATE FUNCTION dbo.Test(@code INT)
RETURNS TABLE
AS RETURN (SELECT * FROM member_table WHERE member_code = @code)
GO
そしてユーザー定義関数を実行すると、以下のような結果が返ります。
SELECT * FROM Test(1)
[結果]
member_code member_name
1 中博俊
ここでテーブルに列を追加し、データを編集します。
[テーブル]member_table
member_code int
memver_class nvarchar(50)
member_name nvarchar(50)
[データ]
member_code member_class member_name
1 代表 中博俊
2 代表 じゃんぬねっと
3 下っ端 nakaP
そして再度ユーザー定義関数を実行すると、以下のような結果に変わります。
SELECT * FROM Test(1)
[結果]
member_code member_name
1 代表
列名と内容が一致していません。なぜでしょうか。
まず、sys.objectsのid列を利用して、sys.columnsの情報を取得します。
(SQL Server 2000 ではsysobjects、syscolumns)
それぞれの詳細は以下をご覧ください。
sys.objects
sys.columns
なお参考にsys.parametersもご覧になられるといいかもしれません。
(SQL Server 2000 ではsysparameters)
sys.parameters
SELECT * FROM sys.columns WHERE object_id = (SELECT object_id FROM sys.objects WHERE name = 'Test')
object_id name column_id ･･･
******* id 1
******* name 2
項目数は多いですが、member_code列とmember_name列が返されることが分かります。
ここでも列名と内容が一致していません。
次にsp_sproc_columnsの情報を見てみます。
sp_sproc_columns
EXEC sp_sproc_column @procedure_name = 'Test'
この結果のTYPE_NAME列を見ると、「table」となっています。
以上の結果より、インラインユーザー定義関数はテーブルを返しますが、戻り値の列数は２つしかないことが分かります。
つまり、テーブル定義の変更後は、３列の結果を２列のテーブルとして返しているのです。
そして、戻り値の列名は便宜上の列名であり、必ずしも元テーブルの列名と同期が取られているわけではないようです。
この問題を解決するには、
１．インラインユーザー定義関数で個別に列名を指定しておく
２．sp_refreshsqlmoduleを実行する
ことになります。
これがバグなのか仕様なのかは分かりませんが、知っていないとハマりそうですね。
「select * は便利だけど便利じゃないよ」
ってことですね。