Posted by bobc on 10/03/07 13:47

> Nevertheless, the procedure you post has an input parameter. If you need
> to retrieve data for more than one code, you need to make multiple calls,
> which is not effecient.

The page would display a dashboard for one selected community at a
time. So, the @CommCode parameter would contain a constant value
throughout the process.

> Really why you go for your lists, I don't know. You could have a single
> procedure that reads all tables and then returns multiple result sets
> that you receive in a dataset of datatables. At least you minimize the
> network roundtrips.

The list was just one idea. I preferred your idea of returning a
dataset.

> > I use bin variables @d1-@d5 rather than literals 1, 7, 20, 25, 60
> > because I'm hoping there is a way to programmatically vary the number
> > and values of bin boundaries...

> That's not that easy. A SELECT query returns a table, and a table has
> fixed number of columns, and each describes a distinct entity. To have
> a variable number of columns or variably named columns, you need to
> engage in dynamic SQL. Which may not be a bad choice for this task,
> presuming that you can deal with the performance issues. But running it
> all in T-SQL is not that fun; VB .Net might be a better venue.

That has been the fundamental question all along. "How complex and/or
flexible can a single stored procedure be, and still be efficient?"

What I'm learning is that they can be very complex, but not as
flexible as c, vb, etc.

> <your cross join example...>
>
> ...put the intervals in a real table, and have
> it configurable.

That is exactly what I have done in previous projects, but I have a
little time in this project to upgrade my sql programming techniques
and thought I could push the envelope.

Thanks for your help, Erland. You have been encouraging and very
helpful. In addition, your quick analysis of, and response to, posts
in this forum is fantastic and very much appreciated. I'll look at
your web site for more interesting reading.

Bob

[Back to original message]