Is a subselect possible on a stored procedure

[kjc]

I have a stored procedure what produces N number of rows.
The rows are ordered by a cataegoryType as follows

catA
catB
catC
What is needed to do on the C++ code side is break these out into
their respective categories by iterating through the rows and checking
the category type. Is there a way to let the DB do this via some sort of
subselect on the rows returned via the stored procedure.

Thanks in advance.

[Erland Sommarskog]

kjc (ks*****@elp.rr.com) writes:

I have a stored procedure what produces N number of rows.
The rows are ordered by a cataegoryType as follows

catA
catB
catC
What is needed to do on the C++ code side is break these out into
their respective categories by iterating through the rows and checking
the category type. Is there a way to let the DB do this via some sort of
subselect on the rows returned via the stored procedure.

I'm sorry, but I don't understand the question. Maybe you could clarify
with an example?

(I do suspect though, that whatever you are trying to do, you should do it
on the the C++ side.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[kjc]

Thanks for the reply.
I guess what I'm trying to say is that the stored procedure returns
what amounts to a heterogeneous collection. If I made three separate
calls to the DB I would not have this problem ie..

call stored_proc_for_typeA
call stored_proc_for_typeB
call stored_proc_for_typeC
But, this is inefficient, so I'm making one call to a stored procedure
to like
call stored_proc_for_AllTypes

But, on the C++ side, these still must be broken out based on the type.


Erland Sommarskog wrote:

kjc (ks*****@elp.rr.com) writes:

I have a stored procedure what produces N number of rows.
The rows are ordered by a cataegoryType as follows

catA
catB
catC
What is needed to do on the C++ code side is break these out into
their respective categories by iterating through the rows and checking
the category type. Is there a way to let the DB do this via some sort of
subselect on the rows returned via the stored procedure.

I'm sorry, but I don't understand the question. Maybe you could clarify
with an example?

(I do suspect though, that whatever you are trying to do, you should do it
on the the C++ side.)

[Erland Sommarskog]

kjc (ks*****@elp.rr.com) writes:

I guess what I'm trying to say is that the stored procedure returns
what amounts to a heterogeneous collection. If I made three separate
calls to the DB I would not have this problem ie..

call stored_proc_for_typeA
call stored_proc_for_typeB
call stored_proc_for_typeC
But, this is inefficient, so I'm making one call to a stored procedure
to like
call stored_proc_for_AllTypes

But, on the C++ side, these still must be broken out based on the type.

I still don't understand what you are after.

Obviously you stored_for_AllTypes can say:

SELECT * FROM tbl WHRE type = 'typeA'
SELECT * FROM tbl WHRE type = 'typeB'
SELECT * FROM tbl WHRE type = 'typeC'

But that is not very much more efficient than making three stored procedure
calls.

But I don't see the problem for the C++ code. You get the data into some
data structure, depending on which client library you use. Using an
ORDER BY clause in the SQL makes it a little simpler for the C++ code
to find the boundaries.

(Actually there is a way to produce more than one result set from query,
by using the COMPUTE BY clause. But I would recommend use of this
deprecated feature.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp