Stored proc. with comma-delimited list as parm. - select all values

[aldovalerio]

I'm looking for a simpler way of selecting a row whose child rows match all values in a comma-delimited list. I'm using SQL Server v7.0 with stored procs. and dynamic SQL (EXEC @sql_string). I pass the string parm. (e.g., 'Bob Smith','Mary White') and expect it to only find a parent rows that has child rows that match 'Bob Smith' AND 'Mary White'. Tables:
1) document
- document_id INTEGER
- document_name VARCHAR(50)
values:
1, "doc. 1"
2, "doc. 2"
3, "doc. 3"

2) document_author
- document_id INTEGER
- author_id INTEGER
values:
2, 1
2, 2
3, 3

3) author
- author_id INTEGER
- author_name VARCHAR(50)
values:
1, 'Bob Smith'
2, 'Mary White'
3, 'Dave Black'

If I pass "'Bob Smith','Mary White'" as parm. I expect it to return "doc. 2", but if I pass "'Bob Smith','Mary White','Dave Black'" I expect it to return 0 rows.

I can parse out the individuals values in the comma-delimited string and dynamically build a statement for each with an exists clause but this is a lot more code than I was hoping to write. I've tested it and it works:

SELECT d.document_id FROM document d
WHERE EXISTS
(SELECT * FROM document_author da1, author a1
WHERE da1.author_id = a1.author_id
AND da1.document_id = d.document_id
AND a1.author_name = 'Bob Smith')
AND EXISTS
(SELECT * FROM document_author da1, author a1
WHERE da1.author_id = a1.author_id
AND da1.document_id = d.document_id
AND a1.author_name = 'Mary White')

With a long list of values this will generate a lot of code and probably overflow my @sql_string variable since this where clause is actually only part of a much longer dynamic query. Any ideas would be much appreciated.

In a sense what I'm looking for is a sort of AND version of the IN ('xxx','yyy') clause.

 

[fluteplr]

pass a second parameter with the number of names you are passing in the first paramenter then:

SELECT d.document_id FROM document d
WHERE @numbernames in
(select count(*) from document_author da1, author a1
WHERE da1.author_id = a1.author_id
AND da1.document_id = d.document_id
AND a1.author_name in ('put delimited string here')

this should work unless one of your authors is listed more than once for the same document.

 

[aldovalerio]

Thanks. Works like a charm.