Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-03-29 : 23:56:19
|
Aldo writes "My subject title probably doesn't describe my problem very well... but what I want to do is: pass a comma-delimited string of values to a stored proc. and find a row where the all values in the parameter string match at least one of the row's child table rows. I'm using SQL Server 7.0 with a stored proc. and dynamic SQL (EXEC @sql_string).
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:
1, 2
1, 4
2, 1
2, 3
3) Author
- author_id INTEGER
- author_name VARCHAR(50)
values:
1, "John Doe"
2, "Bob Smith"
3, "Jane Brown"
4, "Mary White"
If I pass a parameter string of "'Bob Smith','Mary White'", I want to see 1, "doc. 1" as my result; and if I pass "'Bob Smith','Mary White','Jane Brown'" as a parm. I want to see 0 rows returned.
I can achieve this with an EXISTS clause per value in the comma-delimited string, but then I have to parse out the values from the string and dynamically build a potentially lengthy SQL statement, such as the following:
SELECT d.document_id, d.document_name
FROM document d
WHERE EXISTS
(SELECT * FROM document_author da, author a
WHERE da.author_id = a.author_id
AND da.document_id = d.document_id
AND a.author_name = 'Bob Smith')
AND EXISTS
(SELECT * FROM document_author da, author a
WHERE da.author_id = a.author_id
AND da.document_id = d.document_id
AND a.author_name = 'Mary White')
I would like to be able to build a query that uses the comma-delimited string directly in the SQL statement. Any ideas much appreciated." |
|
|
|
|
|
|
|
Select row where child table rows contain all values in comma-delimited list