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

Dynamic report parameters

hells
Does anyone know a way to set up a report with two parameters that the
user can select, where one parameter's populated with data depending on
what's selected in the other parameter?
I'm doing a report of some questionnaire data. I want to let the user
select a question, then select one of the possible responses to that
question. The report will then display answers to *all* questions from
people that gave the selected response to the selected question.
I thought maybe I could use subreports to do this but can't seem to get
that working.
Help much appreciated!
NickTake a look at cascading parameters described here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_interactive_v1_50fn.asp
nthompson@.gmail.com wrote:
> hells
> Does anyone know a way to set up a report with two parameters that the
> user can select, where one parameter's populated with data depending on
> what's selected in the other parameter?
> I'm doing a report of some questionnaire data. I want to let the user
> select a question, then select one of the possible responses to that
> question. The report will then display answers to *all* questions from
> people that gave the selected response to the selected question.
> I thought maybe I could use subreports to do this but can't seem to get
> that working.
> Help much appreciated!
> Nick|||Excellent. I'll have a read. Many thankssql

Dynamic Query with Multi-valued parameter

I'm having problems getting a dynamic query with a multi-valued parameter. I
have a report parameter, defined as multi-value, called deployment_id. I
want to have a dynamic query built with it (because I am eventually going to
also send in a parameter for the WHERE clause).
This works:
SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
R.last_name
FROM o_dpl_communication C INNER JOIN
o_dpl_recipient R ON C.recipient_id = R.recipient_id
WHERE C.deployment_id IN (@.deployment_id)
This does not:
="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
R.last_name " &
"FROM o_dpl_communication C INNER JOIN " &
"o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
"WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
Anyone know how I can fix this dynamic query? Thanks!
StephanieRead the thread Dynamic Conditional report parameters. I show how to do this
... kindof. Because you need single quotes this is not easy (unless your
parameter list is integer, in which case it is quite easy). Anyway, read the
thread and see if it helps you.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> I'm having problems getting a dynamic query with a multi-valued parameter.
> I
> have a report parameter, defined as multi-value, called deployment_id. I
> want to have a dynamic query built with it (because I am eventually going
> to
> also send in a parameter for the WHERE clause).
> This works:
> SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> R.last_name
> FROM o_dpl_communication C INNER JOIN
> o_dpl_recipient R ON C.recipient_id = R.recipient_id
> WHERE C.deployment_id IN (@.deployment_id)
> This does not:
> ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> R.last_name " &
> "FROM o_dpl_communication C INNER JOIN " &
> "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> Anyone know how I can fix this dynamic query? Thanks!
> Stephanie|||try to use:
Join(Parameters!deployment_id.Value, ", ")
the .value return an array, the Join keyword convert the array into a string
with a comma as a separator
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> I'm having problems getting a dynamic query with a multi-valued parameter.
> I
> have a report parameter, defined as multi-value, called deployment_id. I
> want to have a dynamic query built with it (because I am eventually going
> to
> also send in a parameter for the WHERE clause).
> This works:
> SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> R.last_name
> FROM o_dpl_communication C INNER JOIN
> o_dpl_recipient R ON C.recipient_id = R.recipient_id
> WHERE C.deployment_id IN (@.deployment_id)
> This does not:
> ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> R.last_name " &
> "FROM o_dpl_communication C INNER JOIN " &
> "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> Anyone know how I can fix this dynamic query? Thanks!
> Stephanie|||First of all, are you imbedding code directly in the report? that is not
good, since you have to redeploy the whole report should the code need to be
changed. Make a stored procedure instead and pass the list in as a
varchar(255).
Once inside the sproc, all you have to do is parse through the list in a
while loop and create output into a temp table that you can then filter or
output any way you want.
this is way more efficent on the sql server, and will make life easier in
the long run!
"Jeje" wrote:
> try to use:
> Join(Parameters!deployment_id.Value, ", ")
> the .value return an array, the Join keyword convert the array into a string
> with a comma as a separator
>
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> > I'm having problems getting a dynamic query with a multi-valued parameter.
> > I
> > have a report parameter, defined as multi-value, called deployment_id. I
> > want to have a dynamic query built with it (because I am eventually going
> > to
> > also send in a parameter for the WHERE clause).
> >
> > This works:
> > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name
> > FROM o_dpl_communication C INNER JOIN
> > o_dpl_recipient R ON C.recipient_id = R.recipient_id
> > WHERE C.deployment_id IN (@.deployment_id)
> >
> > This does not:
> > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name " &
> > "FROM o_dpl_communication C INNER JOIN " &
> > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> >
> > Anyone know how I can fix this dynamic query? Thanks!
> >
> > Stephanie
>|||That worked perfectly, thanks very much!
Stephanie
"Jeje" wrote:
> try to use:
> Join(Parameters!deployment_id.Value, ", ")
> the .value return an array, the Join keyword convert the array into a string
> with a comma as a separator
>
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> > I'm having problems getting a dynamic query with a multi-valued parameter.
> > I
> > have a report parameter, defined as multi-value, called deployment_id. I
> > want to have a dynamic query built with it (because I am eventually going
> > to
> > also send in a parameter for the WHERE clause).
> >
> > This works:
> > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name
> > FROM o_dpl_communication C INNER JOIN
> > o_dpl_recipient R ON C.recipient_id = R.recipient_id
> > WHERE C.deployment_id IN (@.deployment_id)
> >
> > This does not:
> > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name " &
> > "FROM o_dpl_communication C INNER JOIN " &
> > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> >
> > Anyone know how I can fix this dynamic query? Thanks!
> >
> > Stephanie
>|||Carl,
Thanks for the reply. I knew I could use a stored proc but I needed this to
work on multiple databases. I'm sending in a report parameter of the
database name that I want to report on and I don't want the stored proc to be
in each of the databases.
Anyway, Jeje's answer worked perfectly. Again, thanks for the time.
Stephanie
"Carl Henthorn" wrote:
> First of all, are you imbedding code directly in the report? that is not
> good, since you have to redeploy the whole report should the code need to be
> changed. Make a stored procedure instead and pass the list in as a
> varchar(255).
> Once inside the sproc, all you have to do is parse through the list in a
> while loop and create output into a temp table that you can then filter or
> output any way you want.
> this is way more efficent on the sql server, and will make life easier in
> the long run!
> "Jeje" wrote:
> > try to use:
> > Join(Parameters!deployment_id.Value, ", ")
> >
> > the .value return an array, the Join keyword convert the array into a string
> > with a comma as a separator
> >
> >
> > "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> > news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> > > I'm having problems getting a dynamic query with a multi-valued parameter.
> > > I
> > > have a report parameter, defined as multi-value, called deployment_id. I
> > > want to have a dynamic query built with it (because I am eventually going
> > > to
> > > also send in a parameter for the WHERE clause).
> > >
> > > This works:
> > > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > > R.last_name
> > > FROM o_dpl_communication C INNER JOIN
> > > o_dpl_recipient R ON C.recipient_id = R.recipient_id
> > > WHERE C.deployment_id IN (@.deployment_id)
> > >
> > > This does not:
> > > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > > R.last_name " &
> > > "FROM o_dpl_communication C INNER JOIN " &
> > > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> > > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> > >
> > > Anyone know how I can fix this dynamic query? Thanks!
> > >
> > > Stephanie
> >
> >|||Thanks, Bruce. That's just what I was looking for. And I appreciated seeing
the additional information on the parameters, ie. Value(0), Count, etc.
Stephanie
"Bruce L-C [MVP]" wrote:
> Read the thread Dynamic Conditional report parameters. I show how to do this
> ... kindof. Because you need single quotes this is not easy (unless your
> parameter list is integer, in which case it is quite easy). Anyway, read the
> thread and see if it helps you.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> > I'm having problems getting a dynamic query with a multi-valued parameter.
> > I
> > have a report parameter, defined as multi-value, called deployment_id. I
> > want to have a dynamic query built with it (because I am eventually going
> > to
> > also send in a parameter for the WHERE clause).
> >
> > This works:
> > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name
> > FROM o_dpl_communication C INNER JOIN
> > o_dpl_recipient R ON C.recipient_id = R.recipient_id
> > WHERE C.deployment_id IN (@.deployment_id)
> >
> > This does not:
> > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name " &
> > "FROM o_dpl_communication C INNER JOIN " &
> > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> >
> > Anyone know how I can fix this dynamic query? Thanks!
> >
> > Stephanie
>
>|||thats cool. In that particular case, i would place the dynamic sql inside the
loop and passed in the value. I am glad you have a working solution! :-)
"Stephanie" wrote:
> Carl,
> Thanks for the reply. I knew I could use a stored proc but I needed this to
> work on multiple databases. I'm sending in a report parameter of the
> database name that I want to report on and I don't want the stored proc to be
> in each of the databases.
> Anyway, Jeje's answer worked perfectly. Again, thanks for the time.
> Stephanie
>
> "Carl Henthorn" wrote:
> > First of all, are you imbedding code directly in the report? that is not
> > good, since you have to redeploy the whole report should the code need to be
> > changed. Make a stored procedure instead and pass the list in as a
> > varchar(255).
> > Once inside the sproc, all you have to do is parse through the list in a
> > while loop and create output into a temp table that you can then filter or
> > output any way you want.
> > this is way more efficent on the sql server, and will make life easier in
> > the long run!
> >
> > "Jeje" wrote:
> >
> > > try to use:
> > > Join(Parameters!deployment_id.Value, ", ")
> > >
> > > the .value return an array, the Join keyword convert the array into a string
> > > with a comma as a separator
> > >
> > >
> > > "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> > > news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> > > > I'm having problems getting a dynamic query with a multi-valued parameter.
> > > > I
> > > > have a report parameter, defined as multi-value, called deployment_id. I
> > > > want to have a dynamic query built with it (because I am eventually going
> > > > to
> > > > also send in a parameter for the WHERE clause).
> > > >
> > > > This works:
> > > > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > > > R.last_name
> > > > FROM o_dpl_communication C INNER JOIN
> > > > o_dpl_recipient R ON C.recipient_id = R.recipient_id
> > > > WHERE C.deployment_id IN (@.deployment_id)
> > > >
> > > > This does not:
> > > > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > > > R.last_name " &
> > > > "FROM o_dpl_communication C INNER JOIN " &
> > > > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> > > > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> > > >
> > > > Anyone know how I can fix this dynamic query? Thanks!
> > > >
> > > > Stephanie
> > >
> > >|||I'm pretty sure it only works because the data type of the field is integer.
If it had been string it would not have worked.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:F370A919-A103-499B-8C25-312F9CD42BB7@.microsoft.com...
> thats cool. In that particular case, i would place the dynamic sql inside
> the
> loop and passed in the value. I am glad you have a working solution! :-)
> "Stephanie" wrote:
>> Carl,
>> Thanks for the reply. I knew I could use a stored proc but I needed this
>> to
>> work on multiple databases. I'm sending in a report parameter of the
>> database name that I want to report on and I don't want the stored proc
>> to be
>> in each of the databases.
>> Anyway, Jeje's answer worked perfectly. Again, thanks for the time.
>> Stephanie
>>
>> "Carl Henthorn" wrote:
>> > First of all, are you imbedding code directly in the report? that is
>> > not
>> > good, since you have to redeploy the whole report should the code need
>> > to be
>> > changed. Make a stored procedure instead and pass the list in as a
>> > varchar(255).
>> > Once inside the sproc, all you have to do is parse through the list in
>> > a
>> > while loop and create output into a temp table that you can then filter
>> > or
>> > output any way you want.
>> > this is way more efficent on the sql server, and will make life easier
>> > in
>> > the long run!
>> >
>> > "Jeje" wrote:
>> >
>> > > try to use:
>> > > Join(Parameters!deployment_id.Value, ", ")
>> > >
>> > > the .value return an array, the Join keyword convert the array into a
>> > > string
>> > > with a comma as a separator
>> > >
>> > >
>> > > "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
>> > > news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
>> > > > I'm having problems getting a dynamic query with a multi-valued
>> > > > parameter.
>> > > > I
>> > > > have a report parameter, defined as multi-value, called
>> > > > deployment_id. I
>> > > > want to have a dynamic query built with it (because I am eventually
>> > > > going
>> > > > to
>> > > > also send in a parameter for the WHERE clause).
>> > > >
>> > > > This works:
>> > > > SELECT C.recipient_id, R.customer_id, R.delivery_email,
>> > > > R.first_name,
>> > > > R.last_name
>> > > > FROM o_dpl_communication C INNER JOIN
>> > > > o_dpl_recipient R ON C.recipient_id = R.recipient_id
>> > > > WHERE C.deployment_id IN (@.deployment_id)
>> > > >
>> > > > This does not:
>> > > > ="SELECT C.recipient_id, R.customer_id, R.delivery_email,
>> > > > R.first_name,
>> > > > R.last_name " &
>> > > > "FROM o_dpl_communication C INNER JOIN " &
>> > > > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
>> > > > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
>> > > >
>> > > > Anyone know how I can fix this dynamic query? Thanks!
>> > > >
>> > > > Stephanie
>> > >
>> > >|||sure
in case of an array of string the code is different
something like:
"'" & Join(Parameters!deployment_id.Value, "', '") & "'"
single quote ' added in the join clause + single quote ' added before and
after the Join clause produce:
'toto', 'tata', 'tutu'
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OR2lzWlkHHA.5024@.TK2MSFTNGP06.phx.gbl...
> I'm pretty sure it only works because the data type of the field is
> integer. If it had been string it would not have worked.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:F370A919-A103-499B-8C25-312F9CD42BB7@.microsoft.com...
>> thats cool. In that particular case, i would place the dynamic sql inside
>> the
>> loop and passed in the value. I am glad you have a working solution! :-)
>> "Stephanie" wrote:
>> Carl,
>> Thanks for the reply. I knew I could use a stored proc but I needed
>> this to
>> work on multiple databases. I'm sending in a report parameter of the
>> database name that I want to report on and I don't want the stored proc
>> to be
>> in each of the databases.
>> Anyway, Jeje's answer worked perfectly. Again, thanks for the time.
>> Stephanie
>>
>> "Carl Henthorn" wrote:
>> > First of all, are you imbedding code directly in the report? that is
>> > not
>> > good, since you have to redeploy the whole report should the code need
>> > to be
>> > changed. Make a stored procedure instead and pass the list in as a
>> > varchar(255).
>> > Once inside the sproc, all you have to do is parse through the list in
>> > a
>> > while loop and create output into a temp table that you can then
>> > filter or
>> > output any way you want.
>> > this is way more efficent on the sql server, and will make life easier
>> > in
>> > the long run!
>> >
>> > "Jeje" wrote:
>> >
>> > > try to use:
>> > > Join(Parameters!deployment_id.Value, ", ")
>> > >
>> > > the .value return an array, the Join keyword convert the array into
>> > > a string
>> > > with a comma as a separator
>> > >
>> > >
>> > > "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
>> > > news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
>> > > > I'm having problems getting a dynamic query with a multi-valued
>> > > > parameter.
>> > > > I
>> > > > have a report parameter, defined as multi-value, called
>> > > > deployment_id. I
>> > > > want to have a dynamic query built with it (because I am
>> > > > eventually going
>> > > > to
>> > > > also send in a parameter for the WHERE clause).
>> > > >
>> > > > This works:
>> > > > SELECT C.recipient_id, R.customer_id, R.delivery_email,
>> > > > R.first_name,
>> > > > R.last_name
>> > > > FROM o_dpl_communication C INNER JOIN
>> > > > o_dpl_recipient R ON C.recipient_id = R.recipient_id
>> > > > WHERE C.deployment_id IN (@.deployment_id)
>> > > >
>> > > > This does not:
>> > > > ="SELECT C.recipient_id, R.customer_id, R.delivery_email,
>> > > > R.first_name,
>> > > > R.last_name " &
>> > > > "FROM o_dpl_communication C INNER JOIN " &
>> > > > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
>> > > > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value &
>> > > > ")"
>> > > >
>> > > > Anyone know how I can fix this dynamic query? Thanks!
>> > > >
>> > > > Stephanie
>> > >
>> > >
>|||Good point.
Bruce
"Jeje" <willgart@.hotmail.com> wrote in message
news:4ED05EA4-A55B-480A-BFBB-6B863080B026@.microsoft.com...
> sure
> in case of an array of string the code is different
> something like:
> "'" & Join(Parameters!deployment_id.Value, "', '") & "'"
> single quote ' added in the join clause + single quote ' added before and
> after the Join clause produce:
> 'toto', 'tata', 'tutu'
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OR2lzWlkHHA.5024@.TK2MSFTNGP06.phx.gbl...
>> I'm pretty sure it only works because the data type of the field is
>> integer. If it had been string it would not have worked.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
>> news:F370A919-A103-499B-8C25-312F9CD42BB7@.microsoft.com...
>> thats cool. In that particular case, i would place the dynamic sql
>> inside the
>> loop and passed in the value. I am glad you have a working solution! :-)
>> "Stephanie" wrote:
>> Carl,
>> Thanks for the reply. I knew I could use a stored proc but I needed
>> this to
>> work on multiple databases. I'm sending in a report parameter of the
>> database name that I want to report on and I don't want the stored proc
>> to be
>> in each of the databases.
>> Anyway, Jeje's answer worked perfectly. Again, thanks for the time.
>> Stephanie
>>
>> "Carl Henthorn" wrote:
>> > First of all, are you imbedding code directly in the report? that is
>> > not
>> > good, since you have to redeploy the whole report should the code
>> > need to be
>> > changed. Make a stored procedure instead and pass the list in as a
>> > varchar(255).
>> > Once inside the sproc, all you have to do is parse through the list
>> > in a
>> > while loop and create output into a temp table that you can then
>> > filter or
>> > output any way you want.
>> > this is way more efficent on the sql server, and will make life
>> > easier in
>> > the long run!
>> >
>> > "Jeje" wrote:
>> >
>> > > try to use:
>> > > Join(Parameters!deployment_id.Value, ", ")
>> > >
>> > > the .value return an array, the Join keyword convert the array into
>> > > a string
>> > > with a comma as a separator
>> > >
>> > >
>> > > "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
>> > > news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
>> > > > I'm having problems getting a dynamic query with a multi-valued
>> > > > parameter.
>> > > > I
>> > > > have a report parameter, defined as multi-value, called
>> > > > deployment_id. I
>> > > > want to have a dynamic query built with it (because I am
>> > > > eventually going
>> > > > to
>> > > > also send in a parameter for the WHERE clause).
>> > > >
>> > > > This works:
>> > > > SELECT C.recipient_id, R.customer_id, R.delivery_email,
>> > > > R.first_name,
>> > > > R.last_name
>> > > > FROM o_dpl_communication C INNER JOIN
>> > > > o_dpl_recipient R ON C.recipient_id = R.recipient_id
>> > > > WHERE C.deployment_id IN (@.deployment_id)
>> > > >
>> > > > This does not:
>> > > > ="SELECT C.recipient_id, R.customer_id, R.delivery_email,
>> > > > R.first_name,
>> > > > R.last_name " &
>> > > > "FROM o_dpl_communication C INNER JOIN " &
>> > > > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
>> > > > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value &
>> > > > ")"
>> > > >
>> > > > Anyone know how I can fix this dynamic query? Thanks!
>> > > >
>> > > > Stephanie
>> > >
>> > >
>>
>|||Bruce, cannot locate the thread, believe that info will be very helpful to me.
"Bruce L-C [MVP]" wrote:
> Read the thread Dynamic Conditional report parameters. I show how to do this
> ... kindof. Because you need single quotes this is not easy (unless your
> parameter list is integer, in which case it is quite easy). Anyway, read the
> thread and see if it helps you.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> > I'm having problems getting a dynamic query with a multi-valued parameter.
> > I
> > have a report parameter, defined as multi-value, called deployment_id. I
> > want to have a dynamic query built with it (because I am eventually going
> > to
> > also send in a parameter for the WHERE clause).
> >
> > This works:
> > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name
> > FROM o_dpl_communication C INNER JOIN
> > o_dpl_recipient R ON C.recipient_id = R.recipient_id
> > WHERE C.deployment_id IN (@.deployment_id)
> >
> > This does not:
> > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name " &
> > "FROM o_dpl_communication C INNER JOIN " &
> > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> >
> > Anyone know how I can fix this dynamic query? Thanks!
> >
> > Stephanie
>
>|||Go to Google, click on groups from the down arrow so you search groups then
search on this:
Dynamic Conditional report parameters
You will see it then.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Z-Will" <Z-Will@.discussions.microsoft.com> wrote in message
news:3EFFC67E-1254-4B64-90C7-264C3C43C36B@.microsoft.com...
> Bruce, cannot locate the thread, believe that info will be very helpful to
> me.
> "Bruce L-C [MVP]" wrote:
>> Read the thread Dynamic Conditional report parameters. I show how to do
>> this
>> ... kindof. Because you need single quotes this is not easy (unless your
>> parameter list is integer, in which case it is quite easy). Anyway, read
>> the
>> thread and see if it helps you.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
>> news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
>> > I'm having problems getting a dynamic query with a multi-valued
>> > parameter.
>> > I
>> > have a report parameter, defined as multi-value, called deployment_id.
>> > I
>> > want to have a dynamic query built with it (because I am eventually
>> > going
>> > to
>> > also send in a parameter for the WHERE clause).
>> >
>> > This works:
>> > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
>> > R.last_name
>> > FROM o_dpl_communication C INNER JOIN
>> > o_dpl_recipient R ON C.recipient_id = R.recipient_id
>> > WHERE C.deployment_id IN (@.deployment_id)
>> >
>> > This does not:
>> > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
>> > R.last_name " &
>> > "FROM o_dpl_communication C INNER JOIN " &
>> > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
>> > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
>> >
>> > Anyone know how I can fix this dynamic query? Thanks!
>> >
>> > Stephanie
>>

dynamic query problem

I make a sp in mt sql server,

the SP get one parameter, its the WHERE,
and then I execute the query,

here is my SP:
ALTER PROCEDURE [dbo].[rptEventToPsy]
@.strSQL nvarchar(4000)=''
as
DECLARE @.SQLString NVARCHAR(4000);
SET @.SQLString = N'SELECT
dbo.tbl_CA_meeting.userID, dbo.tblUsersName.OrdName, COUNT(dbo.tbl_CA_event.itemInPackageID) AS Expr1, dbo.tbl_CA_itemInPackage.itemInPackageName, dbo.tbl_CA_package.packageName FROM dbo.tbl_CA_meeting INNER JOIN
dbo.tbl_CA_event ON dbo.tbl_CA_meeting.eventID = dbo.tbl_CA_event.eventID
INNER JOIN
dbo.tbl_CA_itemInPackage ON dbo.tbl_CA_event.itemInPackageID = dbo.tbl_CA_itemInPackage.itemInPackageID
INNER JOIN
dbo.tbl_CA_package ON dbo.tbl_CA_itemInPackage.packageID = dbo.tbl_CA_package.packageID
INNER JOIN
dbo.tblUsersName ON dbo.tbl_CA_meeting.userID = dbo.tblUsersName.UserId
INNER JOIN
dbo.tbl_CA_mishmeret ON dbo.tbl_CA_meeting.mishmeretID = dbo.tbl_CA_mishmeret.mishmeretID ';
SET @.SQLString = @.SQLString + @.strSQL ;
SET @.SQLString = @.SQLString + '
GROUP BY
dbo.tbl_CA_meeting.userID,
dbo.tblUsersName.OrdName,
dbo.tbl_CA_itemInPackage.itemInPackageName,
dbo.tbl_CA_package.packageName';
EXEC sp_ExecuteSql @.SQLString
return;

my problem is in the Reports, I cant to connect the data to the fileds, (the value is empty)

how can I do it?First, your stored procedure is very susceptible to SQL injection. You really need to constrain the input more. There are several articles on the web that will help you. You can accomplish the same result by using an expression for the SQL statement in the report and write some functions to check the input values.sql

dynamic query

hi,

i'm using dynamic query in the dataset because i wanted the result to group by the particular parameter selected.

however, another parameter requires the data to be taken from another table.

is the following syntax acceptable?

=if (Parameters!rev_data = 'A')

begin

"select a,b,c.... from tableA"

end

else

begin

"select d,e,f.... from tableB"

end

i encounter this error : 'Expression expected'...

is the syntax wrong or this way is not possible?

thanks!

I use Stored Procedures to generate dynamic SQL used for reports. The conditional logic is handled in T-SQL to generate and execute the appropriate SQL based on the input parameters. For example, when users want to select the sort fields and sort order for the data in a report.|||Can you give us an example of how you made this work in the stored procedure?

dynamic query

hi,

i'm using dynamic query in the dataset because i wanted the result to group by the particular parameter selected.

however, another parameter requires the data to be taken from another table.

is the following syntax acceptable?

=if (Parameters!rev_data = 'A')

begin

"select a,b,c.... from tableA"

end

else

begin

"select d,e,f.... from tableB"

end

i encounter this error : 'Expression expected'...

is the syntax wrong or this way is not possible?

thanks!

I use Stored Procedures to generate dynamic SQL used for reports. The conditional logic is handled in T-SQL to generate and execute the appropriate SQL based on the input parameters. For example, when users want to select the sort fields and sort order for the data in a report.|||Can you give us an example of how you made this work in the stored procedure?

dynamic query

I need to change the query, according to the parameter I get from url to the reporting services.

For example,

if url =http://localhost:reportserver/parmeter=true?param1=A

then, I need to use a "query1" for the report . If param1 = B, then I need to use "query2".

I would like to know whether this is possible..

thanks,

I suggest using a stored procedure as the dataset for your target report.

The stored procedure would accept as its one parameter the report parameter of the target report and run the required query accordingly.

dynamic query

Hello friends,

I want to create a dynamic query based on input of the parameter.

If the user passes nothing then all fields should be displayed else use query based on parameter.

I had view sample of MSDN ,but I got error [BC30203].

Is there another way to it ?Please help.

I use:

= iif(Parameters!SQLQuery.Value<>"",Parameters!SQLQuery.Value,"SELECT somecolumn from sometable") as "select statement"

and provide a query in the SQLQuery-Parameter..

You could also use:

= iif(Parameters!SomeID.Value<>"","SELECT somecolumn from sometable where id=" & Parameters!SQLQuery.Value,"SELECT somecolumn from sometable where id=123")

|||

Thanks For Your Reply

But I m still confusing.

I had used the iif (condition) in the generic query designer but i cannot retrive the fields which I want from the query.

The Query is executing but the data set does not contain any fields.

for eg:

="Select Idnummer,.....

iif(parameter is null,nothing,"AND ART IN ( " & parameter.value & ")")

Please reply sooner.

|||

You can't check your query anymore, thats right. When writing the query as ="select .. " & some_condition .. Hitting the "!"-Button has no effect.. This statement is evaluated at runtime.. So, you have to go to Preview-Mode and check if the result looks right..

If you are missing the Fields!.. for report design, the easiest way is to execute a "normal" sql-statement once (this will add the fields) and then transform your sql-string..

|||

Dynamic query should be avoided wherever possible. It is much harder to do (as you have seen).

I do this, I have a parameter that says All and returns a value of All (it could also return a number if a number field, just make it a value that does not exist in the database.

Do this:

selct * from sometable where (somefield = @.MyParam or @.MyParam = 'All') and ...

|||

I dunno if this is better, but should do the same thing:

The query will be:

select * from TableName
where FieldName LIKE (CASE WHEN @.param IS NULL THEN '%' ELSE @.param END)

|||

Hello Sir,

How can I implement "ALL" in my parameter

The table field does not contain 'ALL" .

If the parameter selected is 'ALL',then query should execute with the parameter contaning 'ALL' the values.Then my problem could be solve if the parameter contains 'ALL'.

please give a sample to implement 'ALL' in my parameter and in query.

Please reply soon.

Thanks

|||

u can use the query in this way,

SELECT AreaName, AreaCode
FROM Area
UNION
SELECT ' All' AS Areaname, '' AS AreaCode
ORDER BY AreaName

This will add 'All' n ur drop down box n using case statement in query u can get the desired results.

regards

Satyendra

dynamic queries

Hi,

I have a parameter in the url to the report. According to that parameter, I need to change the query. For example, url = http://localhost/reportserver?param1=A

if Param1=A, then I need to use query1 for the report

if param1 = B, then I need to use query2 for the report.

I would like to know if I can do this. If not, is there any other way to build query dynamically in the report.

thanks

Hi,

you can use a stored procedure for this, using the inside procedural logic for deciding which query should be executed.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
sql

Dynamic Parameters selection

this might be a stupid question.

I have a parameter which has a default value set to it. when a second value is selexcted is it necessary to click the view report button.

Could it be set to auto reload on dropdown selection.

I am using reporting Services 2000

Thanks.

Hey guys,

Would there be a runtime load function available for the above problem.

thanks

Dynamic Parameters in 8.5

Will 8.5 allow you to make parameters dynamic.
Specifically is it possible to create a parameter that when that table and or field in SQL is changed, it will update my crystal report with those changes? If not in a parameter is there anyway to do this?If you're going to use Crystal Reports alone that will not be possible since parameters are static.

I don't know how to do it but if you use stored procedures maybe you can make it dynamic.

Dynamic Parameters and using an "All" Value

I have a report that I need to create a parameter of Territories which I can
do with the following Data set...
Territories>>
Select Name
From dbo.territory
and
Contacts>>
SELECT TER.Name AS Territory, CAST(CON.ContactId AS varchar(36)) AS
ContactID, CON.FirstName, CON.LastName, CON.JobTitle, CAD.Line1, CAD.City,
CAD.StateOrProvince, CAD.PostalCode, CON.Telephone1 AS
businessphone, CON.MobilePhone, CON.Telephone3 AS rangephone,
CAST(ACT.AccountId AS varchar(36)) AS AccountID,
ACT.Name AS companyname, CAD.StateOrProvince AS company_stateorprovince
FROM ContactBase CON LEFT OUTER JOIN
CustomerAddressBase CAD ON CON.ContactId = CAD.ParentId AND CAD.AddressNumber = 1 LEFT OUTER JOIN
Account ACT ON CON.AccountId = ACT.AccountId LEFT
OUTER JOIN
CustomerAddressBase CAA ON ACT.AccountId = CAD.ParentId AND CAD.AddressNumber = 2 LEFT OUTER JOIN
TerritoryBase TER ON ACT.TerritoryId = TER.TerritoryId
WHERE (TER.Name IN (@.Territories))
ORDER BY TER.Name, CAD.StateOrProvince, ACT.Name, CON.LastName
I cannot create an All value and use it in the Parameters, for my parameters
values I was doing non Query and setting them to the name, but for All I
tried Northeast,Southest,West. That does not error out, it just doesn't
return any data. The other values work. Very Bizarre.
Any ideas of what I can do? The territory table uses GUIDs as IDs, So I
cannot easily create an integer which could solve the issue.Hi!
We had the same problem but did this.
In your territories select case do the following:
Select '%' as 'Name', 'All' as 'Label'
UNION
Select Name, Name as 'Label'
From dbo.territory and Contacts
This would give you a table looking like
Name Label
% All
Ottawa Ottawa
etc etc
The you would change the dataset statement to :
WHERE (TER.Name LIKE (@.Territories))
Hope this helps,
Erik
"Jack Bender" wrote:
> I have a report that I need to create a parameter of Territories which I can
> do with the following Data set...
> Territories>>
> Select Name
> From dbo.territory
> and
> Contacts>>
> SELECT TER.Name AS Territory, CAST(CON.ContactId AS varchar(36)) AS
> ContactID, CON.FirstName, CON.LastName, CON.JobTitle, CAD.Line1, CAD.City,
> CAD.StateOrProvince, CAD.PostalCode, CON.Telephone1 AS
> businessphone, CON.MobilePhone, CON.Telephone3 AS rangephone,
> CAST(ACT.AccountId AS varchar(36)) AS AccountID,
> ACT.Name AS companyname, CAD.StateOrProvince AS company_stateorprovince
> FROM ContactBase CON LEFT OUTER JOIN
> CustomerAddressBase CAD ON CON.ContactId => CAD.ParentId AND CAD.AddressNumber = 1 LEFT OUTER JOIN
> Account ACT ON CON.AccountId = ACT.AccountId LEFT
> OUTER JOIN
> CustomerAddressBase CAA ON ACT.AccountId => CAD.ParentId AND CAD.AddressNumber = 2 LEFT OUTER JOIN
> TerritoryBase TER ON ACT.TerritoryId = TER.TerritoryId
> WHERE (TER.Name IN (@.Territories))
> ORDER BY TER.Name, CAD.StateOrProvince, ACT.Name, CON.LastName
> I cannot create an All value and use it in the Parameters, for my parameters
> values I was doing non Query and setting them to the name, but for All I
> tried Northeast,Southest,West. That does not error out, it just doesn't
> return any data. The other values work. Very Bizarre.
> Any ideas of what I can do? The territory table uses GUIDs as IDs, So I
> cannot easily create an integer which could solve the issue.
>sql

Dynamic Parameters

I need to build a report in which every field of the dataset acts as a
Parameter to filter data in the report, so that the user is not binded
only to a fixed set of filters.
Based on the datatype of the filter; the condition should be specified
and the user will only supply the value for which he is looking for.
For Example:
In the report i have about 10 fields
EmpSSN No varchar(20)
Emp FirstName varchar(20)
Emp LastName varchar(20)
Emp Location varchar(20)
Emp MartialStatus char(1)
Emp DOB datetime
Emp Salary numeric(18,2)
Emp Designation varchar(20)
Emp Department varchar(20)
Emp Status varchar(20)
When i choose the filter to be as DOB the condition needs to be
generated as >,>=,<,<=,!= and user can define a specific data for the
filter.
Pls advise.Hi,
I am about to start building the exact same report as you outlined
below. did you get any references or whether or not this can be achieved
using reporting services
--
Ciaran
Software developer
"brinda.shree@.gmail.com" wrote:
> I need to build a report in which every field of the dataset acts as a
> Parameter to filter data in the report, so that the user is not binded
> only to a fixed set of filters.
> Based on the datatype of the filter; the condition should be specified
> and the user will only supply the value for which he is looking for.
> For Example:
> In the report i have about 10 fields
> EmpSSN No varchar(20)
> Emp FirstName varchar(20)
> Emp LastName varchar(20)
> Emp Location varchar(20)
> Emp MartialStatus char(1)
> Emp DOB datetime
> Emp Salary numeric(18,2)
> Emp Designation varchar(20)
> Emp Department varchar(20)
> Emp Status varchar(20)
> When i choose the filter to be as DOB the condition needs to be
> generated as >,>=,<,<=,!= and user can define a specific data for the
> filter.
> Pls advise.
>

Dynamic Parameters

I need to be able to schedule a report with a dynamic parameter.
For example,
The user may select "Start of Last Month" as the date parameter value. I
then need to turn this into something like :
CStr(Month(DateAdd("m", -1, Date))) & "/01/" & CStr(Year(DateAdd("m", -1,
Date)))
When scheduling this report, this parameter should then always be "the Start
of Last Month"
I don't know how to make the parameter look at the code as an expression and
not as a String.
Any help would be appreciated.Try,
=CDATE(CStr(Month(DateAdd("m", -1, Date))) & "/01/" &
CStr(Year(DateAdd("m", -1,
Date))))
If it doesn't work, you may also try to create a default value using the
expression above at report manager.
Regards,
Cem Demircioglu
"Tarik Peterson" <tarikp@.investigo.net> wrote in message
news:utVz7JbHFHA.2132@.TK2MSFTNGP14.phx.gbl...
>I need to be able to schedule a report with a dynamic parameter.
> For example,
> The user may select "Start of Last Month" as the date parameter value. I
> then need to turn this into something like :
> CStr(Month(DateAdd("m", -1, Date))) & "/01/" & CStr(Year(DateAdd("m", -1,
> Date)))
> When scheduling this report, this parameter should then always be "the
> Start of Last Month"
> I don't know how to make the parameter look at the code as an expression
> and not as a String.
> Any help would be appreciated.
>|||Another option if you're using stored procedures is to set the start/end date
within the procedure based on a parameter sent through.
"Tarik Peterson" wrote:
> I need to be able to schedule a report with a dynamic parameter.
> For example,
> The user may select "Start of Last Month" as the date parameter value. I
> then need to turn this into something like :
> CStr(Month(DateAdd("m", -1, Date))) & "/01/" & CStr(Year(DateAdd("m", -1,
> Date)))
> When scheduling this report, this parameter should then always be "the Start
> of Last Month"
> I don't know how to make the parameter look at the code as an expression and
> not as a String.
> Any help would be appreciated.
>
>|||Thanks!
I had actually decided to do it this way prior to reading your post, so it
was good to get some kind of confirmation that this was a good way to do it.
Thanks again.
"Dave Klug" <Dave Klug@.discussions.microsoft.com> wrote in message
news:CB4A4573-EB5B-498C-AF2C-11C6FE5CDE00@.microsoft.com...
> Another option if you're using stored procedures is to set the start/end
> date
> within the procedure based on a parameter sent through.
> "Tarik Peterson" wrote:
>> I need to be able to schedule a report with a dynamic parameter.
>> For example,
>> The user may select "Start of Last Month" as the date parameter value. I
>> then need to turn this into something like :
>> CStr(Month(DateAdd("m", -1, Date))) & "/01/" & CStr(Year(DateAdd("m", -1,
>> Date)))
>> When scheduling this report, this parameter should then always be "the
>> Start
>> of Last Month"
>> I don't know how to make the parameter look at the code as an expression
>> and
>> not as a String.
>> Any help would be appreciated.
>>

Dynamic Parameter Question

I have a report that has different layers of resolution: Global, Region, State, Facility and Issue. For different each level
chosen I want to display a different set of parameters and execute a different SP for my dataset (which will return the same rows).
I've seen posts where I can do the dynamic SP calls but I'm stumped on the showing and hiding of the parameters that I need. I
tried putting a =IIF(blah... as the prompt but that just displayed the expression and didn't evaluate it.
ANY help is appreciated to keep me from having to do 5 different reports :)
Thanks
ScottI think to do this you would want cascading parameters. Then when you call
your stored procedure you will need a wrapper sp that determines which to
call.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
news:uzKenwSvEHA.2200@.TK2MSFTNGP11.phx.gbl...
> I have a report that has different layers of resolution: Global, Region,
State, Facility and Issue. For different each level
> chosen I want to display a different set of parameters and execute a
different SP for my dataset (which will return the same rows).
> I've seen posts where I can do the dynamic SP calls but I'm stumped on the
showing and hiding of the parameters that I need. I
> tried putting a =IIF(blah... as the prompt but that just displayed the
expression and didn't evaluate it.
> ANY help is appreciated to keep me from having to do 5 different reports
:)
> Thanks
> Scott
>|||Or you create 1 report with all the parameters and then use linked reports
in which you enable specific parameters.
It's like creating 5 reports, but way faster :-)
Hth,
Tom
"Bruce L-C [MVP]" wrote:
> I think to do this you would want cascading parameters. Then when you call
> your stored procedure you will need a wrapper sp that determines which to
> call.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
> news:uzKenwSvEHA.2200@.TK2MSFTNGP11.phx.gbl...
> > I have a report that has different layers of resolution: Global, Region,
> State, Facility and Issue. For different each level
> > chosen I want to display a different set of parameters and execute a
> different SP for my dataset (which will return the same rows).
> > I've seen posts where I can do the dynamic SP calls but I'm stumped on the
> showing and hiding of the parameters that I need. I
> > tried putting a =IIF(blah... as the prompt but that just displayed the
> expression and didn't evaluate it.
> >
> > ANY help is appreciated to keep me from having to do 5 different reports
> :)
> >
> > Thanks
> > Scott
> >
> >
>
>|||Yeah, but how can I set up my parameters to be cascading like that?
IE
Global requires no parameters
Region requires a FK of a region
State requires a two letter state
Facility requires a facilities key value (int)
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:Ofr5IHTvEHA.1300@.TK2MSFTNGP14.phx.gbl...
>I think to do this you would want cascading parameters. Then when you call
> your stored procedure you will need a wrapper sp that determines which to
> call.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
> news:uzKenwSvEHA.2200@.TK2MSFTNGP11.phx.gbl...
>> I have a report that has different layers of resolution: Global, Region,
> State, Facility and Issue. For different each level
>> chosen I want to display a different set of parameters and execute a
> different SP for my dataset (which will return the same rows).
>> I've seen posts where I can do the dynamic SP calls but I'm stumped on the
> showing and hiding of the parameters that I need. I
>> tried putting a =IIF(blah... as the prompt but that just displayed the
> expression and didn't evaluate it.
>> ANY help is appreciated to keep me from having to do 5 different reports
> :)
>> Thanks
>> Scott
>>
>|||even if you have cascading paprameters, it would still show up on the report
though greyed out, making the report parameter section really cluttered.
Having the IIF at the prompt of the parameters is a real cool add on. Should
be added to the wish list.
I would just create subreports, as suggested later
"Bruce L-C [MVP]" wrote:
> I think to do this you would want cascading parameters. Then when you call
> your stored procedure you will need a wrapper sp that determines which to
> call.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
> news:uzKenwSvEHA.2200@.TK2MSFTNGP11.phx.gbl...
> > I have a report that has different layers of resolution: Global, Region,
> State, Facility and Issue. For different each level
> > chosen I want to display a different set of parameters and execute a
> different SP for my dataset (which will return the same rows).
> > I've seen posts where I can do the dynamic SP calls but I'm stumped on the
> showing and hiding of the parameters that I need. I
> > tried putting a =IIF(blah... as the prompt but that just displayed the
> expression and didn't evaluate it.
> >
> > ANY help is appreciated to keep me from having to do 5 different reports
> :)
> >
> > Thanks
> > Scott
> >
> >
>
>|||I was thinking that these were somehow related. Only certain regions based
on Global, only certain states based on region, only certain facilities
based on State. If so, they you have each of them based on a dataset where
the dataset query parameter is based on the preceding parameter.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
news:Oez$zzbvEHA.716@.TK2MSFTNGP10.phx.gbl...
> Yeah, but how can I set up my parameters to be cascading like that?
> IE
> Global requires no parameters
> Region requires a FK of a region
> State requires a two letter state
> Facility requires a facilities key value (int)
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:Ofr5IHTvEHA.1300@.TK2MSFTNGP14.phx.gbl...
> >I think to do this you would want cascading parameters. Then when you
call
> > your stored procedure you will need a wrapper sp that determines which
to
> > call.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in
message
> > news:uzKenwSvEHA.2200@.TK2MSFTNGP11.phx.gbl...
> >> I have a report that has different layers of resolution: Global,
Region,
> > State, Facility and Issue. For different each level
> >> chosen I want to display a different set of parameters and execute a
> > different SP for my dataset (which will return the same rows).
> >> I've seen posts where I can do the dynamic SP calls but I'm stumped on
the
> > showing and hiding of the parameters that I need. I
> >> tried putting a =IIF(blah... as the prompt but that just displayed the
> > expression and didn't evaluate it.
> >>
> >> ANY help is appreciated to keep me from having to do 5 different
reports
> > :)
> >>
> >> Thanks
> >> Scott
> >>
> >>
> >
> >
>

Dynamic Parameter List

Is it possible to have a dynamic number of parameters? Basically I have a
vertical table that will contain a number of records. As records are added
to this table, they will need to be passed to a stored procedure. So say I
have this structure:
SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.dynamic_param1,
@.dynamic_param2, etc. etc.
Tbl_Dynamic_Params
ID
1 Nationality
2 Net Worth
3 Hair Color
4 Shoe Size
So I want to pass:
SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
@.Net_Worth, @.HairColor, @.ShoeSize.
Is this possible without using dynamic SQL?"James" <neg@.tory.com> wrote in message
news:Ol%23pUYC5HHA.3684@.TK2MSFTNGP02.phx.gbl...
> Is it possible to have a dynamic number of parameters? Basically I have a
> vertical table that will contain a number of records. As records are
> added to this table, they will need to be passed to a stored procedure.
> So say I have this structure:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.dynamic_param1,
> @.dynamic_param2, etc. etc.
> Tbl_Dynamic_Params
> ID
> 1 Nationality
> 2 Net Worth
> 3 Hair Color
> 4 Shoe Size
> So I want to pass:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
> @.Net_Worth, @.HairColor, @.ShoeSize.
> Is this possible without using dynamic SQL?
>
You can have *optional* parameters in a proc, yes. Just specify a default
value for those parameters. For example:
CREATE PROC usp_proc
@.p1 INT = NULL,
@.p2 INT = NULL
AS ...
Both @.p1 and @.p2 are optional and will default to null if they are not
specified. Obviously your parameter *names* must be known at runtime
otherwise you couldn't write any code that used those parameters could you?
Important: Do not use "sp_" as a prefix for your procs. "sp_" is the name
used for system procs and has a special meaning that will cause unwanted
side-effects to your code.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I don't actually use sp as a prefix, it was merely a visual cue. I
understand optional parameters, but I want the list of parameters to be
dynamic, not just the arguments...that's the crux of the problem. I don't
think there's a really good solution to this.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uX5l3nC5HHA.5724@.TK2MSFTNGP05.phx.gbl...
> "James" <neg@.tory.com> wrote in message
> news:Ol%23pUYC5HHA.3684@.TK2MSFTNGP02.phx.gbl...
>> Is it possible to have a dynamic number of parameters? Basically I have
>> a vertical table that will contain a number of records. As records are
>> added to this table, they will need to be passed to a stored procedure.
>> So say I have this structure:
>> SP_Update_Contact @.first_name, @.last_name, @.city, @.state,
>> @.dynamic_param1, @.dynamic_param2, etc. etc.
>> Tbl_Dynamic_Params
>> ID
>> 1 Nationality
>> 2 Net Worth
>> 3 Hair Color
>> 4 Shoe Size
>> So I want to pass:
>> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
>> @.Net_Worth, @.HairColor, @.ShoeSize.
>> Is this possible without using dynamic SQL?
> You can have *optional* parameters in a proc, yes. Just specify a default
> value for those parameters. For example:
>
> CREATE PROC usp_proc
> @.p1 INT = NULL,
> @.p2 INT = NULL
> AS ...
> Both @.p1 and @.p2 are optional and will default to null if they are not
> specified. Obviously your parameter *names* must be known at runtime
> otherwise you couldn't write any code that used those parameters could
> you?
> Important: Do not use "sp_" as a prefix for your procs. "sp_" is the name
> used for system procs and has a special meaning that will cause unwanted
> side-effects to your code.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||"James" <neg@.tory.com> wrote in message
news:eK3xtqC5HHA.5212@.TK2MSFTNGP04.phx.gbl...
>I don't actually use sp as a prefix, it was merely a visual cue. I
>understand optional parameters, but I want the list of parameters to be
>dynamic, not just the arguments...that's the crux of the problem. I don't
>think there's a really good solution to this.
>
I don't understand what you mean when you say you want the "list of
parameters to be dynamic". Parameters have names and types. If you don't
know the names and types in advance then how could you possibly write a proc
that made use of them - even assuming it was possible to pass them as
parameters?
Maybe what you want is an array. SQL Server has a data structure that is
much more powerful than an array: a table! :-)
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> ... but I want the list of parameters to be dynamic, not just the
>> arguments...that's the crux of the problem.
The crux of the problem has to do with the poor design scheme you use to
represent various attributes as values. If you have a static set of
properties that you have to manage, treat them as columns rather than values
in a column. The right answer is that you should reconsider the logical
design. Mixing up data with metadata is always a recipe for messy and
complex solutions.
--
Anith|||You're preaching to the choir. This isn't my design. Let me rewind. A
colleague approach me with this problem.
Contacts Table:
ID First_Name Last_Name ... ...
Pretty basic. Now there is additional data that needs to be stored. I.E.
Shoe Size, Hat Size, and various other data. However, not every record will
have all of these fields. Also, fields need to be added on the fly with the
lowest amount of maintenance possible.
Performance is a huge consideration. He has approximately 2 million records
in the Contacts table and say, 10 fields +/-. He believes that performance
will be better if he has a flat table that is a 1-to-1 with this table that
stores the extra columns...as opposed to a more normalized alternative that
they have in place now which CRAWLS.
Should I talk him out of this redesign, encourage it, or suggest an
alternative? If so, that begs the question...what alternative?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23rOPW9C5HHA.4964@.TK2MSFTNGP06.phx.gbl...
>> ... but I want the list of parameters to be dynamic, not just the
>> arguments...that's the crux of the problem.
> The crux of the problem has to do with the poor design scheme you use to
> represent various attributes as values. If you have a static set of
> properties that you have to manage, treat them as columns rather than
> values in a column. The right answer is that you should reconsider the
> logical design. Mixing up data with metadata is always a recipe for messy
> and complex solutions.
> --
> Anith
>|||"James" <neg@.tory.com> wrote in message
news:eLGgUPD5HHA.5316@.TK2MSFTNGP04.phx.gbl...
> You're preaching to the choir. This isn't my design. Let me rewind. A
> colleague approach me with this problem.
> Contacts Table:
> ID First_Name Last_Name ... ...
> Pretty basic. Now there is additional data that needs to be stored. I.E.
> Shoe Size, Hat Size, and various other data. However, not every record
> will have all of these fields. Also, fields need to be added on the fly
> with the lowest amount of maintenance possible.
> Performance is a huge consideration. He has approximately 2 million
> records in the Contacts table and say, 10 fields +/-. He believes that
> performance will be better if he has a flat table that is a 1-to-1 with
> this table that stores the extra columns...as opposed to a more normalized
> alternative that they have in place now which CRAWLS.
> Should I talk him out of this redesign, encourage it, or suggest an
> alternative? If so, that begs the question...what alternative?
>
Two million rows is not large and should be well within the capabilities of
even an entry level server. If performance is inadequate then start looking
at indexing, query plans, statistics and procedure design.
As for the logical model, a normalized design is the right place to start.
Nothing you have said suggests any need for a change from that.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:elfSovC5HHA.3400@.TK2MSFTNGP03.phx.gbl...
>.
> Maybe what you want is an array. SQL Server has a data structure that is
> much more powerful than an array: a table! :-)
>.
This is a disturbing statement from someone who purports to
understand a strong typed system. I assume its rationale
is not intellectual. Shall I pull out the sql couch and
start the examination? :-)
www.beyondsql.blogspot.com|||You mean like printf. I seriously doubt it considering the hoops C has to go
through to make it work.
How about name/value pairs passed into one large varchar?
set @.paramerters = 'first_name='+@.first_name+';last_name='+@.last_name
create procedure sp_update_contact @.parameters
as
declare @.parameters varchar(max)
loop through the string looking for the ; delimiter, then split the
name/value pair and put it in a table variable:
declare @.MyArray TABLE (
name varchar(20)
value varchar(20)
)
Then do as you please.
Since the name portion had to corospond to a column in the table, you better
have some decent error checking an support for an error return.
"James" <neg@.tory.com> wrote in message
news:Ol%23pUYC5HHA.3684@.TK2MSFTNGP02.phx.gbl...
> Is it possible to have a dynamic number of parameters? Basically I have a
> vertical table that will contain a number of records. As records are
> added to this table, they will need to be passed to a stored procedure.
> So say I have this structure:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.dynamic_param1,
> @.dynamic_param2, etc. etc.
> Tbl_Dynamic_Params
> ID
> 1 Nationality
> 2 Net Worth
> 3 Hair Color
> 4 Shoe Size
> So I want to pass:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
> @.Net_Worth, @.HairColor, @.ShoeSize.
> Is this possible without using dynamic SQL?
>sql

Dynamic Parameter Date Ranges

I require three parameter fields:
1.)Daterange
2.)EndDate
3.)Start Date
Selecting an option from the DateRange Parameter (eg. option = today)
automatically populates the EndDate and StartDate Fields. I also want to give
the user the option to extend the date range to one which is not defined as a
daterange option of desired by editing the EndDate and StartDate Fields.
Any help is appreciated.Hi,
I have exactly the same problem, did you found a solution?
Thanks,
Elisabeth
"SAcanuck" wrote:
> I require three parameter fields:
> 1.)Daterange
> 2.)EndDate
> 3.)Start Date
> Selecting an option from the DateRange Parameter (eg. option = today)
> automatically populates the EndDate and StartDate Fields. I also want to give
> the user the option to extend the date range to one which is not defined as a
> daterange option of desired by editing the EndDate and StartDate Fields.
> Any help is appreciated.|||No I havent found a solution that works like I want it to.
"Elisabeth" wrote:
> Hi,
> I have exactly the same problem, did you found a solution?
> Thanks,
> Elisabeth
> "SAcanuck" wrote:
> > I require three parameter fields:
> >
> > 1.)Daterange
> > 2.)EndDate
> > 3.)Start Date
> >
> > Selecting an option from the DateRange Parameter (eg. option = today)
> > automatically populates the EndDate and StartDate Fields. I also want to give
> > the user the option to extend the date range to one which is not defined as a
> > daterange option of desired by editing the EndDate and StartDate Fields.
> >
> > Any help is appreciated.|||I would have to play with this but it seems like if you have three
parameters and the second and third parameters have an expression as the
default with the expression referencing the first parameter. I can't try
this right now but it should work.
Bruce L-C
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> No I havent found a solution that works like I want it to.
>
> "Elisabeth" wrote:
>> Hi,
>> I have exactly the same problem, did you found a solution?
>> Thanks,
>> Elisabeth
>> "SAcanuck" wrote:
>> > I require three parameter fields:
>> >
>> > 1.)Daterange
>> > 2.)EndDate
>> > 3.)Start Date
>> >
>> > Selecting an option from the DateRange Parameter (eg. option = today)
>> > automatically populates the EndDate and StartDate Fields. I also want
>> > to give
>> > the user the option to extend the date range to one which is not
>> > defined as a
>> > daterange option of desired by editing the EndDate and StartDate
>> > Fields.
>> >
>> > Any help is appreciated.|||Bruce:
I have tried this before but it doesnt perform as expected...
When you run your report the first time and select the first parameter the
other two dates are populated correctly, but when you change your first
parameter (date range) the other dates are not automatically changed.
"Bruce Loehle-Conger" wrote:
> I would have to play with this but it seems like if you have three
> parameters and the second and third parameters have an expression as the
> default with the expression referencing the first parameter. I can't try
> this right now but it should work.
> Bruce L-C
> "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> > No I havent found a solution that works like I want it to.
> >
> >
> > "Elisabeth" wrote:
> >
> >> Hi,
> >>
> >> I have exactly the same problem, did you found a solution?
> >> Thanks,
> >> Elisabeth
> >>
> >> "SAcanuck" wrote:
> >>
> >> > I require three parameter fields:
> >> >
> >> > 1.)Daterange
> >> > 2.)EndDate
> >> > 3.)Start Date
> >> >
> >> > Selecting an option from the DateRange Parameter (eg. option = today)
> >> > automatically populates the EndDate and StartDate Fields. I also want
> >> > to give
> >> > the user the option to extend the date range to one which is not
> >> > defined as a
> >> > daterange option of desired by editing the EndDate and StartDate
> >> > Fields.
> >> >
> >> > Any help is appreciated.
>
>|||Bruce,
I have tried it also and it didn't work.
I hope you will find time to try it...
waiting,
Elisabeth
"SAcanuck" wrote:
> Bruce:
> I have tried this before but it doesnt perform as expected...
> When you run your report the first time and select the first parameter the
> other two dates are populated correctly, but when you change your first
> parameter (date range) the other dates are not automatically changed.
> "Bruce Loehle-Conger" wrote:
> > I would have to play with this but it seems like if you have three
> > parameters and the second and third parameters have an expression as the
> > default with the expression referencing the first parameter. I can't try
> > this right now but it should work.
> >
> > Bruce L-C
> >
> > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> > > No I havent found a solution that works like I want it to.
> > >
> > >
> > > "Elisabeth" wrote:
> > >
> > >> Hi,
> > >>
> > >> I have exactly the same problem, did you found a solution?
> > >> Thanks,
> > >> Elisabeth
> > >>
> > >> "SAcanuck" wrote:
> > >>
> > >> > I require three parameter fields:
> > >> >
> > >> > 1.)Daterange
> > >> > 2.)EndDate
> > >> > 3.)Start Date
> > >> >
> > >> > Selecting an option from the DateRange Parameter (eg. option = today)
> > >> > automatically populates the EndDate and StartDate Fields. I also want
> > >> > to give
> > >> > the user the option to extend the date range to one which is not
> > >> > defined as a
> > >> > daterange option of desired by editing the EndDate and StartDate
> > >> > Fields.
> > >> >
> > >> > Any help is appreciated.
> >
> >
> >|||OK, I just tried this out and in the development environment it does as you
say. However, when I deploy it and try it from the server then it works as
you would expect. Can you try it after deploying it?
Bruce L-C [MVP SQL Server Reporting Services]
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:450111A7-157F-4000-B331-F36758529639@.microsoft.com...
> Bruce:
> I have tried this before but it doesnt perform as expected...
> When you run your report the first time and select the first parameter the
> other two dates are populated correctly, but when you change your first
> parameter (date range) the other dates are not automatically changed.
> "Bruce Loehle-Conger" wrote:
> > I would have to play with this but it seems like if you have three
> > parameters and the second and third parameters have an expression as the
> > default with the expression referencing the first parameter. I can't try
> > this right now but it should work.
> >
> > Bruce L-C
> >
> > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> > > No I havent found a solution that works like I want it to.
> > >
> > >
> > > "Elisabeth" wrote:
> > >
> > >> Hi,
> > >>
> > >> I have exactly the same problem, did you found a solution?
> > >> Thanks,
> > >> Elisabeth
> > >>
> > >> "SAcanuck" wrote:
> > >>
> > >> > I require three parameter fields:
> > >> >
> > >> > 1.)Daterange
> > >> > 2.)EndDate
> > >> > 3.)Start Date
> > >> >
> > >> > Selecting an option from the DateRange Parameter (eg. option =today)
> > >> > automatically populates the EndDate and StartDate Fields. I also
want
> > >> > to give
> > >> > the user the option to extend the date range to one which is not
> > >> > defined as a
> > >> > daterange option of desired by editing the EndDate and StartDate
> > >> > Fields.
> > >> >
> > >> > Any help is appreciated.
> >
> >
> >|||Elisabeth:
Maybe the following link helps you...Im not a SQL Guru (far from it...) so
it doesnt help me much.
http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?pg=4&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&fltr=
"Elisabeth" wrote:
> Bruce,
> I have tried it also and it didn't work.
> I hope you will find time to try it...
> waiting,
> Elisabeth
> "SAcanuck" wrote:
> > Bruce:
> >
> > I have tried this before but it doesnt perform as expected...
> >
> > When you run your report the first time and select the first parameter the
> > other two dates are populated correctly, but when you change your first
> > parameter (date range) the other dates are not automatically changed.
> >
> > "Bruce Loehle-Conger" wrote:
> >
> > > I would have to play with this but it seems like if you have three
> > > parameters and the second and third parameters have an expression as the
> > > default with the expression referencing the first parameter. I can't try
> > > this right now but it should work.
> > >
> > > Bruce L-C
> > >
> > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> > > > No I havent found a solution that works like I want it to.
> > > >
> > > >
> > > > "Elisabeth" wrote:
> > > >
> > > >> Hi,
> > > >>
> > > >> I have exactly the same problem, did you found a solution?
> > > >> Thanks,
> > > >> Elisabeth
> > > >>
> > > >> "SAcanuck" wrote:
> > > >>
> > > >> > I require three parameter fields:
> > > >> >
> > > >> > 1.)Daterange
> > > >> > 2.)EndDate
> > > >> > 3.)Start Date
> > > >> >
> > > >> > Selecting an option from the DateRange Parameter (eg. option = today)
> > > >> > automatically populates the EndDate and StartDate Fields. I also want
> > > >> > to give
> > > >> > the user the option to extend the date range to one which is not
> > > >> > defined as a
> > > >> > daterange option of desired by editing the EndDate and StartDate
> > > >> > Fields.
> > > >> >
> > > >> > Any help is appreciated.
> > >
> > >
> > >|||Hi Bruce:
It does work after deploying... slightly annoying though. Thanks for the
effort.
"Bruce Loehle-Conger [MVP]" wrote:
> OK, I just tried this out and in the development environment it does as you
> say. However, when I deploy it and try it from the server then it works as
> you would expect. Can you try it after deploying it?
> Bruce L-C [MVP SQL Server Reporting Services]
> "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> news:450111A7-157F-4000-B331-F36758529639@.microsoft.com...
> > Bruce:
> >
> > I have tried this before but it doesnt perform as expected...
> >
> > When you run your report the first time and select the first parameter the
> > other two dates are populated correctly, but when you change your first
> > parameter (date range) the other dates are not automatically changed.
> >
> > "Bruce Loehle-Conger" wrote:
> >
> > > I would have to play with this but it seems like if you have three
> > > parameters and the second and third parameters have an expression as the
> > > default with the expression referencing the first parameter. I can't try
> > > this right now but it should work.
> > >
> > > Bruce L-C
> > >
> > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> > > > No I havent found a solution that works like I want it to.
> > > >
> > > >
> > > > "Elisabeth" wrote:
> > > >
> > > >> Hi,
> > > >>
> > > >> I have exactly the same problem, did you found a solution?
> > > >> Thanks,
> > > >> Elisabeth
> > > >>
> > > >> "SAcanuck" wrote:
> > > >>
> > > >> > I require three parameter fields:
> > > >> >
> > > >> > 1.)Daterange
> > > >> > 2.)EndDate
> > > >> > 3.)Start Date
> > > >> >
> > > >> > Selecting an option from the DateRange Parameter (eg. option => today)
> > > >> > automatically populates the EndDate and StartDate Fields. I also
> want
> > > >> > to give
> > > >> > the user the option to extend the date range to one which is not
> > > >> > defined as a
> > > >> > daterange option of desired by editing the EndDate and StartDate
> > > >> > Fields.
> > > >> >
> > > >> > Any help is appreciated.
> > >
> > >
> > >
>
>|||I think something is missing in the link you send to me...
"SAcanuck" wrote:
> Elisabeth:
> Maybe the following link helps you...Im not a SQL Guru (far from it...) so
> it doesnt help me much.
> http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?pg=4&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&fltr=
> "Elisabeth" wrote:
> > Bruce,
> >
> > I have tried it also and it didn't work.
> > I hope you will find time to try it...
> > waiting,
> > Elisabeth
> >
> > "SAcanuck" wrote:
> >
> > > Bruce:
> > >
> > > I have tried this before but it doesnt perform as expected...
> > >
> > > When you run your report the first time and select the first parameter the
> > > other two dates are populated correctly, but when you change your first
> > > parameter (date range) the other dates are not automatically changed.
> > >
> > > "Bruce Loehle-Conger" wrote:
> > >
> > > > I would have to play with this but it seems like if you have three
> > > > parameters and the second and third parameters have an expression as the
> > > > default with the expression referencing the first parameter. I can't try
> > > > this right now but it should work.
> > > >
> > > > Bruce L-C
> > > >
> > > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > > news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> > > > > No I havent found a solution that works like I want it to.
> > > > >
> > > > >
> > > > > "Elisabeth" wrote:
> > > > >
> > > > >> Hi,
> > > > >>
> > > > >> I have exactly the same problem, did you found a solution?
> > > > >> Thanks,
> > > > >> Elisabeth
> > > > >>
> > > > >> "SAcanuck" wrote:
> > > > >>
> > > > >> > I require three parameter fields:
> > > > >> >
> > > > >> > 1.)Daterange
> > > > >> > 2.)EndDate
> > > > >> > 3.)Start Date
> > > > >> >
> > > > >> > Selecting an option from the DateRange Parameter (eg. option = today)
> > > > >> > automatically populates the EndDate and StartDate Fields. I also want
> > > > >> > to give
> > > > >> > the user the option to extend the date range to one which is not
> > > > >> > defined as a
> > > > >> > daterange option of desired by editing the EndDate and StartDate
> > > > >> > Fields.
> > > > >> >
> > > > >> > Any help is appreciated.
> > > >
> > > >
> > > >