Posted by bobc on 10/03/07 17:00

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

[Back to original message]