SQLserverのテーブル値関数の使い方

質問

SQLserver2012でテーブル値関数を作成しています。
その際、実際の値ではなく、テーブルの列名を渡し、外部結合をしたいのですが、
上手くいきません。列名が指定できないのでしょうか。
教えてください。

－－テーブル値関数
CREATE FUNCTION Func1
(
@User_name_no NVARCHAR(6),
@String NVARCHAR(4000),
@Flg NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
　　処理
)
select * from func1('000001','aaaaaaaaaaaa','1');
問題なく処理が行われます。
が
以下の様にテーブルの列名を指定し外部結合すると上手くいきません。
select
B.*
from USER_INFO_TBL A
inner join func1(A.user_no,T1.user_data,T1.flg) B on A.user_no=B.User_no_no;
とか
select
B.*
from USER_INFO_TBL A
inner join (select * from func1(A.user_no,T1.user_data,T1.flg)) B on A.user_no=B.User_no_no;

メッセージ 4104、レベル 16、状態 1、行 4
マルチパート識別子 "A.user_no" をバインドできませんでした。
メッセージ 4104、レベル 16、状態 1、行 4
マルチパート識別子 "A.user_data" をバインドできませんでした。

となります。
どうすればよいか教えてください。お願いします。

回答No.2

noname#212058

回答No.1 です。

失敬、要件を見誤っていました。この目的なら一時テーブルか一時ファンクションを使うのが王道だと思いますが、静的ファンクションに拘るのであれば、以下の SQL でできるかもしれません (未検証です)。

select B.*
from USER_INFO_TBL A
inner join (select User_no_no as join_User_no_no, * from func1(A.user_no,T1.user_data,T1.flg)) B
on A.user_no=B.join_User_no_no;

ただ、この方法が仮にうまくいったとしても、func1 の戻り値の件数が多いとパフォーマンスはボロボロでしょうね。一時テーブルも検討されたほうが良いかと思います。

お礼

ありがとうございます。

教えていただいたSQLで試してみたのですが、やはり以下のエラーがでます。
列名を認識してくれないみたいです。

メッセージ 4104、レベル 16、状態 1、行 4
マルチパート識別子 "A.user_no" をバインドできませんでした。
メッセージ 4104、レベル 16、状態 1、行 4
マルチパート識別子 "A.user_data" をバインドできませんでした。

すみません。
一時テーブルか一時ファンクション
とはどういうものでしょうか。

回答No.1

noname#212058

ストアドファンクションの宣言に、戻り値となるテーブルの列構造が記述されてないからです。以下のページのサンプルを参考にしてみてください。
http://lightgauge.net/database/sqlserver/3007/

補足

ありがとうございます。
もう少し教えてください。
実際にやりたいことは、カンマ区切りのデータを行展開にしたいのです。
つまり、
'001',''
'002','aaa'
'003','aaa,bbb'
'004','aaa,bbb,ccc'
を以下の様に抽出したい
001,1
002,1,aaa
003,1,aaa
003,2,bbb
004,1,aaa
004,2,bbb
004,3,ccc
そこで、
CREATE FUNCTION Split
(
@Usr_id NVARCHAR(6),
@String NVARCHAR(4000),
@Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
WITH Split(stpos,endpos)
AS(
SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
UNION ALL
SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
FROM Split
WHERE endpos > 0
)
SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
'Usr_id' = @Usr_id,
'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
FROM Split
)
GO

としたのですが、実際の値しか渡せず、テーブルの結合で列名を渡して
処理をしたくお聞きしました。

以下の様にしましたがエラーがでます。
CREATE FUNCTION Split
(
@Usr_id NVARCHAR(6),
@String NVARCHAR(4000),
@Delimiter NCHAR(1)
)
RETURNS @retTbl TABLE
(
Id INT,
Usr_id NVARCHAR(6),
Data NVARCHAR(4000)
)
BEGIN
WITH Split(stpos,endpos)
AS(
SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
UNION ALL
SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
FROM Split
WHERE endpos > 0
)
INSERT INTO @retTbl VALUES(
ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
@Usr_id,
SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
)
RETURN
END
GO

メッセージ 4108、レベル 15、状態 1、プロシージャ Split、行 23
ウィンドウ関数は、SELECT 句または ORDER BY 句だけで使用できます。
となります。

ROW_NUMBER() の部分をコメントにすると以下のエラーがでます。
メッセージ 207、レベル 16、状態 1、プロシージャ Split、行 26
列名 'stpos' が無効です。
メッセージ 207、レベル 16、状態 1、プロシージャ Split、行 26
列名 'endpos' が無効です。
メッセージ 207、レベル 16、状態 1、プロシージャ Split、行 26
列名 'endpos' が無効です。
メッセージ 207、レベル 16、状態 1、プロシージャ Split、行 26
列名 'endpos' が無効です。
メッセージ 207、レベル 16、状態 1、プロシージャ Split、行 26
列名 'endpos' が無効です。
メッセージ 207、レベル 16、状態 1、プロシージャ Split、行 26
列名 'stpos' が無効です。

どのようにすれが正しく動きますか。
お願いします。