sql server (alternate): Newbie Question: Can you have multiple Select Statements in a stored proc that interrelate -- sql server (alternate)

Newbie Question: Can you have multiple Select Statements in a stored proc that interrelate

rbrown NO[at]SPAM edium.com
8/28/2003 7:17:48 PM

sql server (alternate): Hi All.

I have been writing simple single stored procedures for a few years
now, but a client needs some reports (under Crystal) that I seem to
think the only way to get the data (from SQL2000) is to build up a
results set using a stored procedure with multiple select statements,
interrelated.

What I mean is this..

I have a Select statement that gathers information from one table.
Lets say 5 fields.

Then, the next select statement uses those two of those five fields to
get more data (lets say 15 fields), which then I have to join with the
data from the first select statement based on the result.

Then, I have another select statement after that, that uses 2 fields
from the second select statement on the 1st and 2nd statement results.

The last step is to pass this all back to either the Crystal reports
or to a vb.aspx application (which I know how to call the stored
procedure from these two apps).

Now, I know it is a big ask, but is there some website, or good
documentation on how to do this (or similar)? I am trying to do too
much with the stored procedure? If you know how to do this, could you
give an example...

Thanks,

Re: Newbie Question: Can you have multiple Select Statements in a stored proc that interrelate

mpir NO[at]SPAM bellsouth.net
8/29/2003 10:26:11 AM

Consider left joins:

select 1.a,1.b,1.c,1.d,1.e,2.f,2.g,2,h,3.i,3.j.....
from (1 left join 1 on 1a=2a and 1b=2b and 1c=2c) left join 3 on 3.a=2.d......

It might work better than a stored procedure.

I have used ADO in VB scripits, but not for asp.

[quoted text, click to view]

Re: Newbie Question: Can you have multiple Select Statements in a stored proc that interrelate

Erland Sommarskog
8/30/2003 8:07:10 PM

[posted and mailed, please reply in news]

Robert Brown (rbrown@edium.com) writes:
[quoted text, click to view]

This does not sound very complicated. You can probably use regular
joins to sort it out, but without knowledge about the queries it is
difficult to tell. One construct you have use for is derived tables.
Derived tables are sort of temp tables, but they are never materialized
and only exist within single query. Extrmely powerful. Here is an
example that lists information about customers, including the
number of orders they have placed:

SELECT C.CustomerID, C.CompanyName, C.Country, O.Ordercnt
FROM Customers C
JOIN (SELECT CustomerID, Ordercnt = COUNT(*)
FROM Orders
GROUP BY CustomerID) AS O ON O.CustomerID = C.CustomerID
ORDER BY O.Ordercnt DESC

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server (alternate) group.