不特定条件での抽出

質問

■使用環境■
開発ソフト：VB6.0
データベース：SQLServer2005

■状況と質問■
テーブル：TB会員
|会員番号|会員名|
|11111111|たなか|
|22222222|さとう|
|33333333|あべ　|
|44444444|かとう|
|55555555|いとう|
|66666666|まりあ|
|77777777|とみこ|

画面：明細型
|22222222▼|さとう|　▼をクリックするとTB会員から会員番号を選択できる
|77777777▼|とみこ|
| ▼| |

ストアド：（簡略化しています）
@会員番号 as long
SELECT * FROM TB会員
WHERE 会員番号 = @会員番号

TB会員から、任意で選択した会員番号に対応する情報を帳票出力したいと思っています。
ストアドを使用して処理をしようとしているのですが・・・
その際にWhere句に当たる部分のパラメータに会員番号を指定しています。
この会員番号が１つではなく、不特定多数になる場合の処理方法について悩んでいます。

また、当方の事情によりワークテーブルを使用したり、nvarchar(MAX)にして[,]区切りでパラメータを渡す等のことができません。
なおかつ、返り値をレコードセット1回で返したいと思っています。
他のストアドファンクションを呼んだりすることは可能です。

こんなわがままに対応できる処理方法はあるのでしょうか？

回答No.2

jamshid6

私も基本No1さんのように自作のSplit関数を使って処理する派なのですが、
>nvarchar(MAX)にして[,]区切りでパラメータを渡す等のことができません。
これは、パラメータは会員番号(int)１つでなくてはならないということですか？もしそうならストアドプロシージャでは無理です。

思いつくのはストアドではなくテーブル関数を使う方法です。
CREATE FUNCTION FUNCTION1(@会員番号 int)
RETURNS TABLE
AS
SELECT * FROM TB会員
WHERE 会員番号 = @会員番号

こんなの作って、VB側で
SELECT * FROM FUNCTION1(会員番号1)
UNION ALL
SELECT * FROM FUNCTION1(会員番号2)
UNION ALL
SELECT * FROM FUNCTION1(会員番号3)
...
というクエリを組み立てて呼べば１つのレコードセットで受け取ることができます。

でもあんまり健全なやり方ではないと思いますので、パラメータをVARCHARに変更ができるなら是非そちらをご検討ください。
パラメータを変更できるなら、Split関数を用意する方法のほかに、動的クエリを使う方法もあります。
DECLARE @sql varchar(max)
SET @sql = 'SELECT * FROM TB会員 WHERE 会員番号 IN ('+@会員番号リスト+')'
EXEC (@sql)

お礼

ご解答ありがとうございます！

私も質問する前に[,]区切りでのストアドは作成してみたのですがサンプルで私が記述したのとは違い、実際に扱う上での選択されるデータ量が多くなる可能性があるので、nvarchar(MAX)にしてしまうと、どれだけのメモリを使うか検討もつかないという状況になってしまうのです。
そのため、[,]区切りを避けていたのですが・・・。

やはり,区切りでファンクション処理を行う方がスマートですよね。
もっとも・・・一番スマートなのはワークテーブルを使うことなのですが・・・。

ありがとうございましたっ！

回答No.1

noname#158371

これがSQL Server 2008なら、TABLE型パラメータとIN句で一発解決なのですが、SQL Server 2005ということなので、ちょっと残念です。

さて、解決方法ですが、普通マッチさせるには
SQL文のIN句を使う必要があるのと、またそのIN句はTABLE型に
対応したものでなければいけません。
しかし、SQL Sever 2005は、TABLE型の引数を受け付けることが
できないので、下記のようなFunctionを作って処理させないと
処理できないです。例えばこんな感じですかね。

1.SQL Server 2005上に、下記のFunctionを作成する
CREATE FUNCTION [dbo].[SplitText]
(
@Texts as nvarchar(max)
)
RETURNS @Table TABLE([Text] varchar(max))
AS
BEGIN

DECLARE @SeparatorChar AS CHAR(1)
DECLARE @NullText AS NVARCHAR(16)
DECLARE @Cnt int;
DECLARE @NextChar nvarchar(1);
DECLARE @SaveText nvarchar(max);

SELECT @SeparatorChar = ',',
@NullText = '',
@Cnt = 1,
@NextChar = '',
@SaveText = '';

WHILE @Cnt <= LEN(@Texts)
BEGIN
SET @NextChar = SUBSTRING(@Texts, @Cnt, 1);
IF @NextChar = @SeparatorChar
BEGIN
IF @SaveText = @NullText SET @SaveText = NULL;
INSERT INTO @Table VALUES(@SaveText);
SET @SaveText = '';
END

IF @NextChar <> @SeparatorChar
BEGIN
SET @SaveText = @SaveText + SUBSTRING(@Texts, @Cnt, 1);
END
SET @Cnt = @Cnt + 1;
END

IF @SaveText = @NullText SET @SaveText = NULL;

INSERT INTO @Table VALUES(@SaveText);

RETURN
END

2.作成しているストアドの引数の型をINTではなく、NVARCHAR(MAX)
　に変更する

ALTER PROCEDURE [dbo].[XXXXX]
(@会員番号 NVARCHAR(MAX))
AS .....

3.作成しているストアドから1.で作成したFunctionを呼び出すよう
　修正する

SELECT * FROM TB会員
WHERE 会員番号 IN(SELECT * FROM [dbo].[SplitText](@会員番号))

これでOKなのですが、作成したストアドを呼び出す時は、
検索したい複数の会員番号を ","（カンマ）で区切って下さい。

例：
DECLARE @会員番号 NVARCHAR(MAX)
SET @会員番号 = '1,2,3,4,5,6,7,8'
EXEC [スキーマ名].[作成したストアド] @会員番号

こんな感じで対応できるのであれば、ご検討下さい。

＃なお上記のFunctionは、
　http://blogs.wankuma.com/mura/archive/2008/01/07/116304.aspx
　で公開されていますが、私が若干アレンジ加えております。

ご参考になれば幸いです。

参考URL：

http://blogs.wankuma.com/mura/archive/2008/01/07/116304.aspx

お礼

ご解答ありがとうございますっ！
私も質問する前に、同じソースを参考にして[,]区切りのパラメータを元に抽出するように作成したのですが・・・
選択されたデータ量が多い場合、メモリの使用量が増えてしまうため、今回は別の方法がないかと探していました。

ご解答頂いたストアドは別の処理でもお世話になったのですが
今回参考にできなかったのが非常に残念です。

ご解答ありがとうございましたっ！！