Complicated Dynamic Query... a better way?

Just getting started in RS. I've got this dynamic query string:
="select * from v_teds_rpt_workorderlisting where teds_project_nbr=" &
Parameters!teds_project_nbr.Value & iif(Parameters!status.Value="A",
"", " and status='" & Parameters!status.Value &"'") &
iif(Parameters!activity_id.Value=0, "", " and activity_id=" &
Parameters!activity_id.Value) & iif(
IsNumeric(Parameters!w_o_nbr.Value), " and w_o_nbr=" &
Parameters!w_o_nbr.Value, "") &
iif(IsNumeric(Parameters!feas_log_number.Value), " and
feas_log_number=" & Parameters!feas_log_number.Value, "") &
iif(Parameters!component_nbr.Value=0, "", " and component_nbr=" &
Parameters!component_nbr.Value) & iif(Right(Parameters!filter.Value,
2)=":I" and isnumeric(Parameters!filter_on.Value), " and " &
Left(Parameters!filter.Value, Len(Parameters!filter.Value)-2) & "=" &
Parameters!filter_on.Value, "") & iif(Right(Parameters!filter.Value,
2)=":S", " and " & Left(Parameters!filter.Value,
Len(Parameters!filter.Value)-2) & "='" & Parameters!filter_on.Value &
"'", "") & iif(Right(Parameters!filter.Value, 2)=":D" and
IsDate(Parameters!filter_on.Value), " and " &
Left(Parameters!filter.Value, Len(Parameters!filter.Value)-2) & "='" &
Trim(Parameters!filter_on.Value) & "'", "") &" order by " &
Parameters!sort_on.Value & " " & Parameters!sort_order.Value
Nuts, eh? I have to do type checking on a few of the fields because
we have a "Filter On" Drop down which allows the user to pick any
field in the table to filter by. Therefore, I have to typecheck the
text box where they enter the values to make sure a date is a date, a
number is a number, etc.
Would it be better to pass all the parameters into a stored procedure
and build the query there? The script above took a while to make
because it's so easy to misplace a paren or a quote.
Would love to see a shortcut to the Parameters!xxx.Value reference as
well, perhaps P!xxx.valueI haven't fully analyzed your script, however yes a sProc is a viable way to
go...
Further, I suggest either using an sProc that just validates the the types and
then either exec's the working sProc, or returns a helpfull msg, and/or put a
.Net web page as the UI and render the report from your .Net app/aspx page.
HTH
JeffP....
"Larry Bud" <larrybud2002@.yahoo.com> wrote in message
news:5db363e0.0407271034.1dcc4f94@.posting.google.com...
> Just getting started in RS. I've got this dynamic query string:
> ="select * from v_teds_rpt_workorderlisting where teds_project_nbr=" &
> Parameters!teds_project_nbr.Value & iif(Parameters!status.Value="A",
> "", " and status='" & Parameters!status.Value &"'") &
> iif(Parameters!activity_id.Value=0, "", " and activity_id=" &
> Parameters!activity_id.Value) & iif(
> IsNumeric(Parameters!w_o_nbr.Value), " and w_o_nbr=" &
> Parameters!w_o_nbr.Value, "") &
> iif(IsNumeric(Parameters!feas_log_number.Value), " and
> feas_log_number=" & Parameters!feas_log_number.Value, "") &
> iif(Parameters!component_nbr.Value=0, "", " and component_nbr=" &
> Parameters!component_nbr.Value) & iif(Right(Parameters!filter.Value,
> 2)=":I" and isnumeric(Parameters!filter_on.Value), " and " &
> Left(Parameters!filter.Value, Len(Parameters!filter.Value)-2) & "=" &
> Parameters!filter_on.Value, "") & iif(Right(Parameters!filter.Value,
> 2)=":S", " and " & Left(Parameters!filter.Value,
> Len(Parameters!filter.Value)-2) & "='" & Parameters!filter_on.Value &
> "'", "") & iif(Right(Parameters!filter.Value, 2)=":D" and
> IsDate(Parameters!filter_on.Value), " and " &
> Left(Parameters!filter.Value, Len(Parameters!filter.Value)-2) & "='" &
> Trim(Parameters!filter_on.Value) & "'", "") &" order by " &
> Parameters!sort_on.Value & " " & Parameters!sort_order.Value
>
> Nuts, eh? I have to do type checking on a few of the fields because
> we have a "Filter On" Drop down which allows the user to pick any
> field in the table to filter by. Therefore, I have to typecheck the
> text box where they enter the values to make sure a date is a date, a
> number is a number, etc.
> Would it be better to pass all the parameters into a stored procedure
> and build the query there? The script above took a while to make
> because it's so easy to misplace a paren or a quote.
> Would love to see a shortcut to the Parameters!xxx.Value reference as
> well, perhaps P!xxx.value