Strongly Typed Datasets 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

Cor
Thu May 08 11:14:04 CDT 2008

Ben,

It is very simple.

A strongly typed dataset is created from an XSD.

To make an XSD you can use a resultset from a table (select * from table) or
an other SP, however the SP itself is not in the strongly typed dataset
itself.

It can be in things as the table adapter, however for those the queries are
very limited and in fact some additions to the strongly typed dataset, which
is in all Net versions complete compatible with each other.

Cor

"Ben" <Ben@discussions.microsoft.com> schreef in bericht
news:C92B4D94-63A7-4FE8-9C1B-0EB261E8FFB7@microsoft.com...
> 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
>