Dynamic Select list in Stored Proc

I have the following script where the select parameters is dependent on the "component" parameter list being send via the stored proc. Therefore the number of parameters returned by the proc is dynamic. These fields therefore do not show in the .Net design to be added to the report. Need some advise please.
CREATE PROCEDURE dbo.prcPerfAnalysisComponentTest
( @.Category varchar(50)
, @.Item varchar(50)
, @.Component1 varchar(250)
, @.Component2 varchar(250) = NULL
, @.Component3 varchar(250) = NULL
, @.Component4 varchar(250) = NULL
, @.Component5 varchar(250) = NULL
, @.Component6 varchar(250) = NULL
, @.Component7 varchar(250) = NULL
, @.Component8 varchar(250) = NULL
, @.Component9 varchar(250) = NULL
, @.Component10 varchar(250) = NULL
, @.DOW char(13) = NULL
, @.StartTime char(8) = NULL -- Eg. '00:00:00'
, @.EndTime char(8) = NULL -- Eg. '23:59:59'
, @.GroupInd char(7) = 'DAY' ) -- DAY, WEEK, MONTH, YEAR,
QUARTER
AS
DECLARE @.Command varchar(2000)
SELECT @.Command = 'SELECT ' +
CASE
WHEN @.GroupInd = 'WEEK' THEN 'LTRIM(STR(TimeYear)) + ''-'' + LTRIM(STR(TimeWeek))'
WHEN @.GroupInd = 'MONTH' THEN 'LTRIM(STR(TimeYear)) + ''-'' + LTRIM(STR(TimeMonth))'
WHEN @.GroupInd = 'QUARTER' THEN 'LTRIM(STR(TimeYear)) + ''-'' + LTRIM(STR(TimeQuarter))'
WHEN @.GroupInd = 'YEAR' THEN 'LTRIM(STR(TimeYear))'
ELSE 'LEFT(t.TimePortion,5)' --Default to daily
END
SELECT @.Command = @.Command +
', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component1 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component1 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component1 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component1 + '"'
IF @.Component2 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component2 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component2 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component2 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component2 + '"'
IF @.Component3 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component3 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component3 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component3 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component3 + '"'
IF @.Component4 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component4 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component4 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component4 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component4 + '"'
IF @.Component5 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component5 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component5 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component5 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component5 + '"'
IF @.Component6 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component6 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component6 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component6 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component6 + '"'
IF @.Component7 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component7 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component7 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component7 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component7 + '"'
IF @.Component8 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component8 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component8 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component8 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component8 + '"'
IF @.Component9 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component9 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component9 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component9 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component9 + '"'
IF @.Component10 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component10 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component10 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component10 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component10 + '"'
SELECT @.Command = @.Command + ' FROM DataSN3 p ' +
'JOIN vwServerComponent sc ON sc.ServerComponentID = p.ServerComponentID AND sc.MonitorItemID = p.MonitorItemID ' +
'JOIN TimeLineSN3 t ON p.MonitorDate = t.TimeLineID WHERE sc.CategoryDescription = ''' + @.Category + ''' ' +
'AND sc.ItemDescription = ''' + @.Item + ''' '
IF @.DOW IS NOT NULL
SELECT @.Command = @.Command + ' AND t.TimeDOW IN (' + @.DOW + ')'
IF @.StartTime IS NOT NULL
SELECT @.Command = @.Command + ' AND t.TimePortion >= (''' + @.StartTime + ''')'
IF @.EndTime IS NOT NULL
SELECT @.Command = @.Command + ' AND t.TimePortion <= (''' + @.EndTime + ''')'
IF @.Start IS NOT NULL
SELECT @.Command = @.Command + ' AND t.TimeDate >= CONVERT(datetime, ''' + CONVERT(char(19), @.Start, 121) + ''', 121)'
IF @.End IS NOT NULL
SELECT @.Command = @.Command + ' AND t.TimeDate <= CONVERT(datetime, ''' + CONVERT(char(19), @.End, 121) + ''', 121)'
SELECT @.Command = @.Command + ' GROUP BY ' +
CASE
WHEN @.GroupInd = 'WEEK' THEN 'LTRIM(STR(TimeYear)) + ''-'' + LTRIM(STR(TimeWeek)) '
WHEN @.GroupInd = 'MONTH' THEN 'LTRIM(STR(TimeYear)) + ''-'' + LTRIM(STR(TimeMonth)) '
WHEN @.GroupInd = 'QUARTER' THEN 'LTRIM(STR(TimeYear)) + ''-'' + LTRIM(STR(TimeQuarter)) '
WHEN @.GroupInd = 'YEAR' THEN 'LTRIM(STR(TimeYear)) '
ELSE 'LEFT(t.TimePortion,5) ' --Default to daily
END +
' ORDER BY 1'
EXECUTE (@.Command)You don't have to have the fields showing in the Designer to use them. You
can set the source of the textboxes to =Fields!<your field name>.Value. You
can also use the IsMissing property of the Field object to determine if the
stored procedure has returned the field or not. Finally, please not that
multiple select statements are not supported in RS; only the first is used.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Frans-sa" <Frans-sa@.discussions.microsoft.com> wrote in message
news:B7AB19DC-FB72-43C9-9EF6-665BA46F4CA2@.microsoft.com...
> I have the following script where the select parameters is dependent on
the "component" parameter list being send via the stored proc. Therefore
the number of parameters returned by the proc is dynamic. These fields
therefore do not show in the .Net design to be added to the report. Need
some advise please.
>
> CREATE PROCEDURE dbo.prcPerfAnalysisComponentTest
> ( @.Category varchar(50)
> , @.Item varchar(50)
> , @.Component1 varchar(250)
> , @.Component2 varchar(250) = NULL
> , @.Component3 varchar(250) = NULL
> , @.Component4 varchar(250) = NULL
> , @.Component5 varchar(250) = NULL
> , @.Component6 varchar(250) = NULL
> , @.Component7 varchar(250) = NULL
> , @.Component8 varchar(250) = NULL
> , @.Component9 varchar(250) = NULL
> , @.Component10 varchar(250) = NULL
> , @.DOW char(13) = NULL
> , @.StartTime char(8) = NULL -- Eg. '00:00:00'
> , @.EndTime char(8) = NULL -- Eg. '23:59:59'
> , @.GroupInd char(7) = 'DAY' ) -- DAY, WEEK, MONTH, YEAR,
> QUARTER
> AS
> DECLARE @.Command varchar(2000)
> SELECT @.Command = 'SELECT ' +
> CASE
> WHEN @.GroupInd = 'WEEK' THEN 'LTRIM(STR(TimeYear)) + ''-'' +
LTRIM(STR(TimeWeek))'
> WHEN @.GroupInd = 'MONTH' THEN 'LTRIM(STR(TimeYear)) + ''-'' +
LTRIM(STR(TimeMonth))'
> WHEN @.GroupInd = 'QUARTER' THEN 'LTRIM(STR(TimeYear)) + ''-'' +
LTRIM(STR(TimeQuarter))'
> WHEN @.GroupInd = 'YEAR' THEN 'LTRIM(STR(TimeYear))'
> ELSE 'LEFT(t.TimePortion,5)' --Default to daily
> END
> SELECT @.Command = @.Command +
> ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component1 + ''' THEN 1
ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' +
@.Component1 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN
sc.ComponentName = ''' + @.Component1 + ''' THEN 1 ELSE 0 END) END AS "' +
@.Component1 + '"'
> IF @.Component2 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component2 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component2 + ''' THEN p.MonitorValue ELSE 0 END) /
SUM(CASE WHEN sc.ComponentName = ''' + @.Component2 + ''' THEN 1 ELSE 0 END)
END AS "' + @.Component2 + '"'
> IF @.Component3 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component3 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component3 + ''' THEN p.MonitorValue ELSE 0 END) /
SUM(CASE WHEN sc.ComponentName = ''' + @.Component3 + ''' THEN 1 ELSE 0 END)
END AS "' + @.Component3 + '"'
> IF @.Component4 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component4 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component4 + ''' THEN p.MonitorValue ELSE 0 END) /
SUM(CASE WHEN sc.ComponentName = ''' + @.Component4 + ''' THEN 1 ELSE 0 END)
END AS "' + @.Component4 + '"'
> IF @.Component5 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component5 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component5 + ''' THEN p.MonitorValue ELSE 0 END) /
SUM(CASE WHEN sc.ComponentName = ''' + @.Component5 + ''' THEN 1 ELSE 0 END)
END AS "' + @.Component5 + '"'
> IF @.Component6 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component6 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component6 + ''' THEN p.MonitorValue ELSE 0 END) /
SUM(CASE WHEN sc.ComponentName = ''' + @.Component6 + ''' THEN 1 ELSE 0 END)
END AS "' + @.Component6 + '"'
> IF @.Component7 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component7 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component7 + ''' THEN p.MonitorValue ELSE 0 END) /
SUM(CASE WHEN sc.ComponentName = ''' + @.Component7 + ''' THEN 1 ELSE 0 END)
END AS "' + @.Component7 + '"'
> IF @.Component8 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component8 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component8 + ''' THEN p.MonitorValue ELSE 0 END) /
SUM(CASE WHEN sc.ComponentName = ''' + @.Component8 + ''' THEN 1 ELSE 0 END)
END AS "' + @.Component8 + '"'
> IF @.Component9 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component9 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component9 + ''' THEN p.MonitorValue ELSE 0 END) /
SUM(CASE WHEN sc.ComponentName = ''' + @.Component9 + ''' THEN 1 ELSE 0 END)
END AS "' + @.Component9 + '"'
> IF @.Component10 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component10 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component10 + ''' THEN p.MonitorValue ELSE 0 END)
/ SUM(CASE WHEN sc.ComponentName = ''' + @.Component10 + ''' THEN 1 ELSE 0
END) END AS "' + @.Component10 + '"'
> SELECT @.Command = @.Command + ' FROM DataSN3 p ' +
> 'JOIN vwServerComponent sc ON sc.ServerComponentID = p.ServerComponentID
AND sc.MonitorItemID = p.MonitorItemID ' +
> 'JOIN TimeLineSN3 t ON p.MonitorDate = t.TimeLineID WHERE
sc.CategoryDescription = ''' + @.Category + ''' ' +
> 'AND sc.ItemDescription = ''' + @.Item + ''' '
> IF @.DOW IS NOT NULL
> SELECT @.Command = @.Command + ' AND t.TimeDOW IN (' + @.DOW + ')'
> IF @.StartTime IS NOT NULL
> SELECT @.Command = @.Command + ' AND t.TimePortion >= (''' + @.StartTime +
''')'
> IF @.EndTime IS NOT NULL
> SELECT @.Command = @.Command + ' AND t.TimePortion <= (''' + @.EndTime +
''')'
> IF @.Start IS NOT NULL
> SELECT @.Command = @.Command + ' AND t.TimeDate >= CONVERT(datetime, ''' +
CONVERT(char(19), @.Start, 121) + ''', 121)'
> IF @.End IS NOT NULL
> SELECT @.Command = @.Command + ' AND t.TimeDate <= CONVERT(datetime, ''' +
CONVERT(char(19), @.End, 121) + ''', 121)'
> SELECT @.Command = @.Command + ' GROUP BY ' +
> CASE
> WHEN @.GroupInd = 'WEEK' THEN 'LTRIM(STR(TimeYear)) + ''-'' +
LTRIM(STR(TimeWeek)) '
> WHEN @.GroupInd = 'MONTH' THEN 'LTRIM(STR(TimeYear)) + ''-'' +
LTRIM(STR(TimeMonth)) '
> WHEN @.GroupInd = 'QUARTER' THEN 'LTRIM(STR(TimeYear)) + ''-'' +
LTRIM(STR(TimeQuarter)) '
> WHEN @.GroupInd = 'YEAR' THEN 'LTRIM(STR(TimeYear)) '
> ELSE 'LEFT(t.TimePortion,5) ' --Default to daily
> END +
> ' ORDER BY 1'
> EXECUTE (@.Command)
>