Strongly Typed Dataset accessing query using 'IN'   

This problem touches both SQL/Server and ADO.NET, and its solution could be 
in either.  A round of applause if you can provide the solution.

The goal:
Produce a stored procedure which accepts a string of options to be used in a 
SELECT WHERE <a field> IN (<the string> ) statement, AND make the resulting 
set of rows available as a strongly typed dataset.

What I’ve tried so far and why it doesn’t work:
1 – pass the string into the stored procedure, build a varchar containing 
the entire select statement, and execute that string
	Doesn’t work because VS / ADO.NET sees the ‘exec’ command, not a ‘select’ 
command, so when I drop the sProc onto the dataset page in VS2008, it becomes 
part of the QueriesTableAdapter, not its own table adapter, as if it were a 
SQL command that doesn’t return rows.

2 – use option 1 but select into a temporary table, then follow that with a 
SELECT * FROM #tempTable.
	Doesn’t work because the scope of the temporary table that the Select 
creates is limited to the ‘exec’ command, and the subsequent ‘select (*)’ 
command can’t access the temporary table.
3 – use option 2 but use a global temporary table
	Won’t work because multiple simultaneous accesses will collide, trying to 
simultaneously access the single global temporary table

date: Thu, 8 May 2008 06:58:07 -0700   author:   Ben