SEO

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,
Robert

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.


rbrown@edium.com (Robert Brown) wrote in message news:<bdd1819.0308281817.13ff73f6@posting.google.c om>...
> 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,
> Robert

[posted and mailed, please reply in news]

Robert Brown (rbrown@edium.com) writes:
> 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).

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
http://www.microsoft.com/sql/techinf...2000/books.asp