Posted by bobc on 10/01/07 15:18

Thank you all for your help. I think my biggest mistake was to not
start at the beginning. Please accept my apology for any lack of
clarity in my posts. I'm still learning how to communicate about
these issues.

And yes, Tony... I'm after engineering, not quick and dirty code. Not
sure who your criticism is aimed at, but I have been referring to an
input array string since the first post, where the pseudo-code
condition on the loop indicated that the actual array length could
vary. I thought I could simplify things(only 5 elements) and not
burden others with a full explanation, but I probably need to rethink
that. Nevertheless, thanks for your time and help.

An overview of my project follows, but to review...
1. I posted to get help with my method of parsing an input string
2. The parsing code would be in the sub_proc that actually does the
work(not the wrapper)
3. I've been writing simple stored procedures and functions for years,
but I'm new at this level of complexity
4. My ultimate goal is an engineered solution that reflects the best
sql programming practices

Here is an example of what I'm trying to do: Populate a grid(below)
on a .net dashboard web page with counts of various types of
activity(y axis) that would be distributed into bins(x axis) . The
datasource would be a single table or simple view(no aggregates). The
bins would represent increments in specific criteria, which would NOT
necessarily involve sequential values such as days of the month. (The
actual page will contain several grids, each having a different number
of bins. I would like to use the same code for all grids, if possible
-- sending the datasource, number of bins, and bin criteria as input
parameters.)

1 2 3 4 5 6 7 8 9 10 11 12 13 14
15...
activity1 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0
activity2 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0
activity3 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0
activity4 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0
....
activity20 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0

I could call 20 different stored procedures, each having n SELECT
statements, but we all know a whole list of reasons why that's a bad
idea.

I could build a temporary table on the db server and return it as a
dataset to the page, but don't know the best way to build the
temporaty table. Ideally, the process would be flexible enough to
handle the whole job, and not require a set of 20+ procs that do the
same thing in slightly different ways -- costing more time, resources,
and maintenance. But I envision an enormous SELECT statement for each
activity. Maybe that's what it shoud be, and I'm just underestimating
sql server.

Where this post started:

My thought was to call a wrapper from the vb.net page, and the wrapper
would contain a set of EXECs calling the sub proc I sketched for you
earlier. One EXEC for each type of activity(activity.1 to
activity.n).

So, the vb.net page would call the wrapper, sending (for example) 20
"array" string parameters in which to return the results. This would
require opening only one connection to the db only one time -- one of
my goals.

Then the wrapper would sequentially call the sub-proc 20 times -- once
for each activity type. The sub-proc would parse the input array to
acquire the bin criteria, loop through the input array for each bin
and execute a dynamically built SELECT in an EXEC() statement, and
finally return an array for the current activity type. (Although it
would simplify maintenance, everyone has made it abundantly clear that
efficiency would be lost, and I would burn resources on the server as
well.)

When the wrapper finished, it would return 20 array strings to the
vb.net page. The page would parse the array strings and display the
results through some type of asp.net data control.

The consensus of returning a set makes more sense all the time. I
could just populate datalists from .net tables or datasets. But, I
feel like I'm back at square one, looking for an effecient way to
build the set on the db server when both my input arrays and bin
criteria can vary. Maybe my illustration of the final product above
will be more useful to anyone who has the time and interest to take
another look.

With much respect and gratitude,

BobC

[Back to original message]