I want to parse @ArrayOfDays into @d1 through @d5

In my stored procedure, I want to parse @ArrayOfDays into @d1 through
@d5.

@ArrayOfDays is a varchar input parameter containing,
for example, "1.7.21.25.60." - five elements.

Most active vars:
@i - loop counter
@char - current char in string
@tempVal - contains the current element as it is being built
@tempValExecString - contains SELECT stmt for EXEC()

I'm using EXEC() to execute a dynamically built SELECT.

The error I get when calling from vb.net is:
Must declare the variable '@tempVal'.

Two manual traces indicate the logic is ok.

I suspect my assignment statement for @tempValExecString.

Any help would be appreciated. - BobC

----------------------------------------------------------
DECLARE
@d1 varchar(3),
@d2 varchar(3),
@d3 varchar(3),
@d4 varchar(3),
@d5 varchar(3),
@i int,
@char char(1),
@tempVal varchar(3),
@tempValExecString varchar(30)

SELECT @tempVal = ''
SELECT @i = 1

WHILE @i < LEN(@ArrayOfDays)
BEGIN
SELECT @char = SUBSTRING(@ArrayOfDays, @i, 1)
WHILE @char <> '.'
BEGIN
SELECT @tempVal = @tempVal + @char
SELECT @char = SUBSTRING(@ArrayOfDays, @i+1, 1)
IF @char = '.'
BEGIN
/* the following should produce "SELECT @d1 = 1" when it reads the
first period(.) */
SELECT @tempValExecString = 'SELECT @d' + LTRIM(RTRIM(STR(@i))) + '
= @tempVal'
EXEC(@tempValExecString)
SELECT @tempVal = ''
SELECT @i = @i + 1
END
SELECT @i = @i + 1
END
END
----------------------------------------------------------

Re: I want to parse @ArrayOfDays into @d1 through @d5

bobc (bcanavan@fmbnewhomes.com) writes:
> In my stored procedure, I want to parse @ArrayOfDays into @d1 through
> @d5.
>
> @ArrayOfDays is a varchar input parameter containing,
> for example, "1.7.21.25.60." - five elements.
>
> Most active vars:
> @i - loop counter
> @char - current char in string
> @tempVal - contains the current element as it is being built
> @tempValExecString - contains SELECT stmt for EXEC()
>
> I'm using EXEC() to execute a dynamically built SELECT.

Wait a minute. You are in a relational database now, not in a C++ program.

I didn't ask why you returned a delimited string in the procedure in
your first post, but if you intend on unpack the string in the
calling procedure, you are on the wrong track altogther. Pass the
data in a table, and perform your operations on the whole set.

> The error I get when calling from vb.net is:
> Must declare the variable '@tempVal'.
>
> Two manual traces indicate the logic is ok.

No, it's not. A batch of dynamic SQL is a scope of its own, and you
cannot access variables in outer scope. If you want to assign
variables @d1 to @d5, that's five SELECT statements.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: I want to parse @ArrayOfDays into @d1 through @d5

>> In my stored procedure, I want to parse @ArrayOfDays into @d1 through @d5 .. execute a dynamically built SELECT. <<

You are doing almost everything wrong. SQL does not work this way.
Just pass a simple five parameter list. Then clean up your data in the
procedure body. Try this for a skeleton

CREATE PROCEDURE Foobar
(@d1 INTEGER, @d2 INTEGER, @d3 INTEGER, @d4 INTEGER, @d5 INTEGER)
AS
SELECT ..
FROM Floob
WHERE Floob.x IN (@d1, @d2, @d3, @d4, @d5)
AND ..;

YOU can use COALESCE in the IN() list to handle NULLs or whatever.

Re: I want to parse @ArrayOfDays into @d1 through @d5

On Sep 28, 8:52 pm, --CELKO-- wrote:
> >> In my stored procedure, I want to parse @ArrayOfDays into @d1 through @d5 .. execute a dynamically built SELECT. <<
>
> You are doing almost everything wrong. SQL does not work this way.
> Just pass a simple five parameter list. Then clean up your data in the
> procedure body. Try this for a skeleton
>
> CREATE PROCEDURE Foobar
> (@d1 INTEGER, @d2 INTEGER, @d3 INTEGER, @d4 INTEGER, @d5 INTEGER)
> AS
> SELECT ..
> FROM Floob
> WHERE Floob.x IN (@d1, @d2, @d3, @d4, @d5)
> AND ..;
>
> YOU can use COALESCE in the IN() list to handle NULLs or whatever.

Thanks, all. Both posts (yesterday and today) have been part of my
experiments to find the most efficient method of querying a ton of
data a ton of different ways to populate a "dashboard" page on
our .net intranet. You've both discovered that I am more an
applications programmer than a sql programmer, so I know you'll
forgive me. Meanwhile, your input has been very helpful. Thanks for
your time. -BobC

ps: es, the output string "array" was intended to be parsed by my
vb.net app. I just got a little curious about how arrays could be
implemented in t-sql, and possibly save some calls to the db server by
my app, or at least reduce the number of batches. I'm sure it's all
been done before, but I had to try and fail for myself. A learning
experience if nothing else. Thanks again.

Re: I want to parse @ArrayOfDays into @d1 through @d5

> CREATE PROCEDURE Foobar
> (@d1 INTEGER, @d2 INTEGER, @d3 INTEGER, @d4 INTEGER, @d5 INTEGER)
> AS
> SELECT ..
> FROM Floob
> WHERE Floob.x IN (@d1, @d2, @d3, @d4, @d5)
> AND ..;
>
> YOU can use COALESCE in the IN() list to handle NULLs or whatever.
>

Amazing, you just don't learn do you.

What is the risk and resource assessment of adding value number 6?

Resource assessment...

1) Change the stored procedure to accept an extra parameter
2) Change the query IN to accept an extra parameter
3) Change all the applications that call the stored procedure to accept
the extra parameter - that can be '1' to 'n' in a real environment where
applications share common logic (stored procedures).

Risk assessment...

1) Database changes - requires application to be taken offline while the
release to add the extra parameter is done
2) Application changes - each application binary needs to be updated to
use the new parameter; for fat clients that would be quite an involved task
for a couple of thousand clients even with SMS.
3) Testing - did you capture all applications using the procedure, each
application requires a test plan and testing.

Now, if you had used CSV instead - you'd pass a single parameter to the
stored procedure containing 1 to 'n' values then you wouldn't have any of
the above, it would just work; there would be no requirement to take the
application offline, there would be no risk that some clients didn't get
updated properly so weren't using the correct version of the executable
etc...

I really do wish you'd start listening to people who actually do this type
of thing day in day out and have done so for 20 + years, sitting writing
books for 30+ years and teaching people is no replacement for solid
industrial experience.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]


"--CELKO--" wrote in message
news:1191027166.216455.160600@50g2000hsm.googlegroups.com...
>>> In my stored procedure, I want to parse @ArrayOfDays into @d1 through
>>> @d5 .. execute a dynamically built SELECT. <<
>
> You are doing almost everything wrong. SQL does not work this way.
> Just pass a simple five parameter list. Then clean up your data in the
> procedure body. Try this for a skeleton
>
> CREATE PROCEDURE Foobar
> (@d1 INTEGER, @d2 INTEGER, @d3 INTEGER, @d4 INTEGER, @d5 INTEGER)
> AS
> SELECT ..
> FROM Floob
> WHERE Floob.x IN (@d1, @d2, @d3, @d4, @d5)
> AND ..;
>
> YOU can use COALESCE in the IN() list to handle NULLs or whatever.
>

Re: I want to parse @ArrayOfDays into @d1 through @d5

The proc below will take your CSV input and put it into a table #csv_split
which you can then extract each @d variable for.

Is there a specific reason you need @d1, @d2 etc... what are you trying to
do? It might be possible to do it set wise.

Tony.

CREATE PROC array_parse
@csv varchar(8000) = ',1,7,21,25,60'
AS
BEGIN

SET @csv = ltrim(rtrim(@csv))

IF PATINDEX( '%[^,0-9]%', @csv ) > 0 -- Checks to make sure input is
digits or comma to prevent SQL injection
BEGIN
RAISERROR( 'Injection attempt or invalid data.', 16, 1 )

RETURN

END

DECLARE @d1 varchar(3)
DECLARE @d2 varchar(3)
DECLARE @d3 varchar(3)
DECLARE @d4 varchar(3)
DECLARE @d5 varchar(3)
DECLARE @d6 varchar(3)
DECLARE @d7 varchar(3)

DECLARE @sql nvarchar(4000)

CREATE TABLE #csv_split (
pos int not null PRIMARY KEY IDENTITY,
data int not null
)

SET @sql = REPLACE( @csv, ',', CHAR(13) + CHAR(10) + 'insert #csv_split
( data ) values( ' )

SET @sql = REPLACE( @sql, CHAR(13), ' );' + CHAR(13) )

SET @sql = RIGHT( @sql, LEN( @sql ) - 5 ) + ' );'

EXEC( @sql )

SET @d1 = ( SELECT data FROM #csv_split WHERE pos = 1 )
SET @d2 = ( SELECT data FROM #csv_split WHERE pos = 2 )
SET @d3 = ( SELECT data FROM #csv_split WHERE pos = 3 )
SET @d4 = ( SELECT data FROM #csv_split WHERE pos = 4 )
SET @d5 = ( SELECT data FROM #csv_split WHERE pos = 5 )
SET @d6 = ( SELECT data FROM #csv_split WHERE pos = 6 )
SET @d7 = ( SELECT data FROM #csv_split WHERE pos = 7 )

SELECT @d1, @d2, @d3, @d4, @d5, @d6, @d7

end

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]


"bobc" wrote in message
news:1191015416.228609.59050@50g2000hsm.googlegroups.com...
> In my stored procedure, I want to parse @ArrayOfDays into @d1 through
> @d5.
>
> @ArrayOfDays is a varchar input parameter containing,
> for example, "1.7.21.25.60." - five elements.
>
> Most active vars:
> @i - loop counter
> @char - current char in string
> @tempVal - contains the current element as it is being built
> @tempValExecString - contains SELECT stmt for EXEC()
>
> I'm using EXEC() to execute a dynamically built SELECT.
>
> The error I get when calling from vb.net is:
> Must declare the variable '@tempVal'.
>
> Two manual traces indicate the logic is ok.
>
> I suspect my assignment statement for @tempValExecString.
>
> Any help would be appreciated. - BobC
>
> ----------------------------------------------------------
> DECLARE
> @d1 varchar(3),
> @d2 varchar(3),
> @d3 varchar(3),
> @d4 varchar(3),
> @d5 varchar(3),
> @i int,
> @char char(1),
> @tempVal varchar(3),
> @tempValExecString varchar(30)
>
> SELECT @tempVal = ''
> SELECT @i = 1
>
> WHILE @i < LEN(@ArrayOfDays)
> BEGIN
> SELECT @char = SUBSTRING(@ArrayOfDays, @i, 1)
> WHILE @char <> '.'
> BEGIN
> SELECT @tempVal = @tempVal + @char
> SELECT @char = SUBSTRING(@ArrayOfDays, @i+1, 1)
> IF @char = '.'
> BEGIN
> /* the following should produce "SELECT @d1 = 1" when it reads the
> first period(.) */
> SELECT @tempValExecString = 'SELECT @d' + LTRIM(RTRIM(STR(@i))) + '
> = @tempVal'
> EXEC(@tempValExecString)
> SELECT @tempVal = ''
> SELECT @i = @i + 1
> END
> SELECT @i = @i + 1
> END
> END
> ----------------------------------------------------------
>

Re: I want to parse @ArrayOfDays into @d1 through @d5

>> I just got a little curious about how arrays could be implemented in T-SQL, .. <<

The concept of arrays, linked lists, etc. do not exist in SQL -- just
tables. Ever work with LISP? No arrays, and it uses recursion
instead of loops. And LISP only has lists.

SQL is very much a foreign language to the procedural programmer. For
example, in Japanese, there are no articles or plurals, the pronoun
system is totally different (no direct equivalent to first person
singular, etc), the verb tenses are totally different and sentences
have a topic, but not a subject, etc.). But millions of people still
use Japanese.

You can fake a matrix with this skeleton:

CREATE TABLE Array
(i INTEGER NOT NULL CHECK (i BETWEEN 1 and 10),
j INTEGER NOT NULL CHECK (i BETWEEN 1 and 10),
vali INTEGER NOT NULL);

For INTEGER ARRAY A[1:10, 1:10] in a procedural language, but then you
have to write your or own library functions, loop constructs, etc.
And performance will stink.

Re: I want to parse @ArrayOfDays into @d1 through @d5

bobc (bcanavan@fmbnewhomes.com) writes:
> ps: es, the output string "array" was intended to be parsed by my
> vb.net app.

Also in that case it would be better to return a result set and receive
that in a datatable in VB .Net. The code for composing the list is
dependent on the number of elements being fixed. Add one more value,
and you have a maintenance job to do. With result set + datatable you have
not.

> I just got a little curious about how arrays could be
> implemented in t-sql, and possibly save some calls to the db server by
> my app, or at least reduce the number of batches. I'm sure it's all
> been done before, but I had to try and fail for myself.

You may be interested in this link for a rainy day:
http://www.sommarskog.se/arrays-in-sql.html.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: I want to parse @ArrayOfDays into @d1 through @d5

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

Re: I want to parse @ArrayOfDays into @d1 through @d5

> 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

110% @ celko and his proposed solution - the guy is an idiot with little
real industrial experience based on his solutions....

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

Re: I want to parse @ArrayOfDays into @d1 through @d5

bobc (bcanavan@fmbnewhomes.com) writes:
> 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.)
>...
> 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.

Why would you have different procedures for different activities?

I will have to admit that I don't get a very good understanding of
what you are trying to achieve. But a standard recommendation is that
you post:

1) CREATE TABLE statement for your table(s).
2) INSERT statements with sample data.
3) The desired result given the sample.

Of course, this assumes that the data model is set, and neither that is
clear to me. Then again, if you post what you have now, we may get a
better grip of where you're heading.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: I want to parse @ArrayOfDays into @d1 through @d5

> > 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.
>
> Why would you have different procedures for different activities?

Thanks for your patience, Erland. In the statment above, I was
stepping through my thought process for you -- moving from the simple
method of executing many slightly different SELECTs, to a black box
solution that can handle all bins for all activities. The black box
is what I'm aiming for.

> Of course, this assumes that the data model is set, and neither that is
> clear to me. Then again, if you post what you have now, we may get a
> better grip of where you're heading.

The data model is set. I will rewrite my post today, providing the
information you've asked for.

Thanks again very much.

BobC

Re: I want to parse @ArrayOfDays into @d1 through @d5

I hope this makes the problem more understandable. Thanks to anyone
who takes an interest.

I want to display a series of grids on a .net web page.

The grids would make up a dashboard that provides a snapshot of sales
and marketing activities, customer status information, forecasts and
objectives, etc.

Activities are grouped into grids by customer status (lead, active
customer, under contract, closed buyer) because the activities and
milestones (represented by the bins) vary for each status.

Activites(1-n) in a single grid are not all pulled from the same table
or view, and some of the criteria would differ. So I don't believe a
GROUP BY clause on a single dataset would fit the problem.

In the example below, activity1 represents sales leads that are
currently 1, 7, 20, 25 and 60 days old, respectively. These values
will come from the Prospects table.

Activity2 might represent forecasts for current sales leads at the
same intervals. These values would come from a sales objectives
table.


1 7 20 25 60
activity1 0 0 0 0 0
activity2 0 0 0 0 0
activity3 0 0 0 0 0
activity4 0 0 0 0 0
....
activityn 0 0 0 0 0

Another grid might have fewer or more bins.

My goal is to minimize:
# open connections to the database
# calls to stored procedures
length of code in stored procedures or udf's
maintenance as activities or bins are added or dropped

My "procedural language programmer" solution would be to open one
connection to the db and grab all the information at once, retrieving
all the activity rows in individual activity parameters. I could then
parse each parameter value into the appropriate bins with vb behind
the page.

Another approach, which Erland suggested, is to return a dataset.

I have listed (below) simplified versions of the Prospect table
description and the proc GetLeadsByStatusAge.

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, through input parameters or some other
means, and make this thing elegant. In other words, I'd like to
create a black box that can process any activity I ask it to,
regardless of where the data comes from, how many bins apply, or what
the bin boundaries are.

GetLeadsByStatusAge returns an array string, but could just as easily
insert a row into a temporary table.

Thanks to Erland for suggesting the use of CASE. I've used it before
in a similar way, but sometimes forget its power.

Thanks to everyone who takes a look at it.

BobC

/ * Simplified definition of the Prospects table: */

CREATE TABLE Prospects (
CommunityCode varchar (3),
LastName1 varchar (30),
FirstName1 varchar (15),
InitialContactDate datetime,
ProspectStatus varchar (1),
StatusChangeDate datetime
)
GO

/* Simplified procedure intended to return one row of the grid on
my .net page. */

CREATE PROCEDURE GetLeadsByStatusAge
@CommCode char(3),
@Result varchar(255) OUTPUT

AS

SELECT @Result =

(SELECT
ltrim(str(SUM(CASE
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @d1 AND
@d2-1
WHEN TRUE THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @d2 AND
@d3-1
WHEN TRUE THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @d3 AND
@d4-1
WHEN TRUE THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @d4 AND
@d5-1
WHEN TRUE THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) >= @d5
WHEN TRUE THEN 1 ELSE 0 END))) + '.'


FROM prospects
WHERE CommunityCode = @CommCode
AND prospectstatus='L'
)

GO

Re: I want to parse @ArrayOfDays into @d1 through @d5

CORRECTION: syntax of the case statement was wrong in last post.
Shoud be...

CREATE PROCEDURE GetLeadsByStatusAge
@CommCode char(3),
@Result varchar(255) OUTPUT

AS

SELECT @Result =

(SELECT
ltrim(str(SUM(CASE WHEN
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @d1 AND
@d2-1
THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE WHEN
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @d2 AND
@d3-1
THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE WHEN
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @d3 AND
@d4-1
THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE WHEN
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @d4 AND
@d5-1
THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE WHEN
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) >= @d5
THEN 1 ELSE 0 END))) + '.'


FROM prospects
WHERE CommunityCode = @CommCode
AND prospectstatus='L'
)

GO

Re: I want to parse @ArrayOfDays into @d1 through @d5

bobc (bcanavan@fmbnewhomes.com) writes:
> Activites(1-n) in a single grid are not all pulled from the same table
> or view, and some of the criteria would differ. So I don't believe a
> GROUP BY clause on a single dataset would fit the problem.

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.

> My "procedural language programmer" solution would be to open one
> connection to the db and grab all the information at once, retrieving
> all the activity rows in individual activity parameters. I could then
> parse each parameter value into the appropriate bins with vb behind
> the page.

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.

> 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, through input parameters or some other
> means, and make this thing elegant.

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.

But there is another approach: don't return columns return rows. Here is
a simple example:

SELECT P.CommunityCode, b.d1,
SUM(CASE WHEN DATEDIFF(day, P.StatusChangeDate, CURRENT_TIMESTAMP)
BETWEEN b.d1 AND coalesce(b.d2, 10000000)
THEN 1
ELSE 0
END)
FROM Prospects P
CROSS JOIN (SELECT d1 = 1, d2 = 6
UNION ALL
SELECT 7, 19
UNION ALL
SELECT 20, 24
UNION ALL
SELECT 25, 59
UNION ALL
SELECT 60, NULL) AS b
WHERE P.ProspectStatus = 'L'
GROUP BY P.CommunityCode, b.d1

Here I have put the date intervals in a derived table, but I guess you
can see where this leads: put the intervals in a real table, and have
it configurable.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: I want to parse @ArrayOfDays into @d1 through @d5

> 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.

>
>
> ...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

Re: I want to parse @ArrayOfDays into @d1 through @d5

Not giving up yet! I wrote a new proc that would return one row, and
I think looks very flexible. I can vary the number of bins and their
values, the data source, the WHERE clause of the SELECT it builds, and
even accomodate differences in column names from one data source to
another(a date field in this case). It could be called by a wrapper
to build a set of rows. The wrapper would execute a set of EXEC()'s
building a dataset, and then return the dataset to my vb.net page.

Open one db connection one time, get all the data back in one package,
eliminate verbose code.

I could build all the input parameters in vb and send them to the
wrapper, and vary the number of calls the wrapper makes to the
subproc. That way, I would only have to change the code in my page
when the number or value of bins change. Wouldn't have to edit the
stored procs.

Does it look like a reasonable solution to you? Is it reasonably
efficient?

Bob

------------------------------------------------------------ -------------------------------------

CREATE PROCEDURE [dbo].[commdash_activity_by_age]
@ArrayOfBins varchar(255), -- an input array string of variable
size(number of elements)
@ArraySize int, -- number of elements
@DataSourceName varchar(50), -- the data source name (table, view,
other stored proc)
@DateFieldName varchar(25), -- the date field name differs in some
source tables
@WhereClause varchar(255) -- the predetermined WHERE clause for the
SELECT

AS

DECLARE
@WorkingArray varchar(255), -- copy of the input array string,
truncated from the left as bin elements are read
@WorkingArrayLength int, -- actual length of the string
@FirstDelimiter int, -- position of first bin delimiter in
WorkingArray
@col int, -- index of current column in the SELECT clause
@LowerBin varchar(3),
@UpperBin varchar(3),
@SelectClause varchar(4000), -- the select clause of the final query
to be executed
@query varchar(4000) -- the final query to be executed

SET @WorkingArray = @ArrayOfBins
SET @WorkingArrayLength = LEN(@WorkingArray)
SET @col = 1
SET @LowerBin = ''
SET @UpperBin = ''
SET @SelectClause = 'SELECT '

-- get the first bin value from the input array string
SET @FirstDelimiter = CHARINDEX('.', @WorkingArray)
SET @LowerBin = SUBSTRING(@WorkingArray, 1, @FirstDelimiter - 1)
-- remove the leading bin value and its trailing delimiter from the
front of @ArrayOfBins
SET @WorkingArray = SUBSTRING(@WorkingArray, @FirstDelimiter + 1,
@WorkingArrayLength - @FirstDelimiter)

-- loop through the appending of column statements @ArraySize times
(number of elements)
WHILE @col < @ArraySize
BEGIN

-- get the upper bin value from the input array string
SET @FirstDelimiter = CHARINDEX('.', @WorkingArray)
SET @UpperBin = SUBSTRING(@WorkingArray, 1, @FirstDelimiter - 1)
-- remove the leading bin value and its trailing delimiter from the
front of @ArrayOfBins
SET @WorkingArray = SUBSTRING(@WorkingArray, @FirstDelimiter + 1,
@WorkingArrayLength - @FirstDelimiter)

-- append the next column
SET @SelectClause = @SelectClause + 'ltrim(str(SUM(CASE WHEN
DATEDIFF(day, ' + @DateFieldName + ', CURRENT_TIMESTAMP) BETWEEN ' +
@LowerBin + ' AND ' + STR(CONVERT(integer, @UpperBin)-1) +
'THEN 1 ELSE 0 END))), '

-- shift @LowerBin up to @UpperBin in preparation for next column
SET @LowerBin = @UpperBin
-- advance to the next column
SET @col = @col + 1

IF @col = @ArraySize -- append the column for the last bin, then
exit the loop.
BEGIN
SET @SelectClause = @SelectClause + 'ltrim(str(SUM(CASE WHEN
DATEDIFF(day, ' + @DateFieldName + ', CURRENT_TIMESTAMP) > ' +
@LowerBin +
'THEN 1 ELSE 0 END))) '
END

END

-- append the FROM and WHERE clauses
SET @query = @SelectClause + 'FROM ' + @DataSourcename + ' WHERE ' +
@WhereClause

-- execute the query
EXEC(@query)
GO

Re: I want to parse @ArrayOfDays into @d1 through @d5

bobc (bcanavan@fmbnewhomes.com) writes:
> 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.

Depends on what you mean with flexible, but with regards to column and
tables, yes, SQL puts you into a straight-jacket. But there is stuff
which is a lot of work to do in a traditional language which is a breeze
in SQL.

> I could build all the input parameters in vb and send them to the
> wrapper, and vary the number of calls the wrapper makes to the
> subproc. That way, I would only have to change the code in my page
> when the number or value of bins change. Wouldn't have to edit the
> stored procs.
>
> Does it look like a reasonable solution to you? Is it reasonably
> efficient?

Except that this far you have not really gained of the strength of a
stored procedure. You could just as well have built that SQL string
in client code. And then use a real array, with need for any list.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx