dynamic SQL as report source

I would like to use a stored procedure with parameters which creates and
executes dynamic sql as a source for a report.
I get an error, though, stating
"could not generate a list of fields for the query".
I tried adding the fields manually to the report definition and matching
them with the fields in the dynamic SQL, but I received error messages like:
"The value expression for the textbox 'blah' refers to the field 'blah'.
Report item expressions can only refer to fields within the current data set
scope. ..."
I guess I can just put all the sql into the dataset directly, but it would
be easier to manage in a stored procedure.
It needs to be dynamic because I have to build a dynamic Order By clause
with different alternative data types, so I can't use a Case statement.
The stored procedure is something like this:
Create Procedure sp
@.param1 int,
@.param2 int
as
declare @.sqlString (varchar(200))
set @.sqlString = 'select col1, col2
from tbl1
where col1 = ' + @.param1 +
'col2 = ' + @.param2
exec @.sqlString
Thanks!
Billwhat degree of dinamic is the order by clause you are making ... I mean, it
ends up being 3 or 4 cases, or the caller has complete control which column
to order?
Why do I say that? ... well, if there are 3 or 4 cases, u can put the static
part of the query in a user defined function, and have 3 or 4 cases where u
return a select of the function with a static order clause ...
Other than that, I cant think of anything ...
"bill" wrote:
> I would like to use a stored procedure with parameters which creates and
> executes dynamic sql as a source for a report.
> I get an error, though, stating
> "could not generate a list of fields for the query".
> I tried adding the fields manually to the report definition and matching
> them with the fields in the dynamic SQL, but I received error messages like:
> "The value expression for the textbox 'blah' refers to the field 'blah'.
> Report item expressions can only refer to fields within the current data set
> scope. ..."
> I guess I can just put all the sql into the dataset directly, but it would
> be easier to manage in a stored procedure.
> It needs to be dynamic because I have to build a dynamic Order By clause
> with different alternative data types, so I can't use a Case statement.
> The stored procedure is something like this:
> Create Procedure sp
> @.param1 int,
> @.param2 int
> as
> declare @.sqlString (varchar(200))
> set @.sqlString => 'select col1, col2
> from tbl1
> where col1 = ' + @.param1 +
> 'col2 = ' + @.param2
> exec @.sqlString
> Thanks!
> Bill
>
>|||Yes you have to add them manually or type them each time...
"bill" <belgie@.datamti.com> wrote in message
news:eHX3q2CnEHA.3684@.TK2MSFTNGP10.phx.gbl...
> I would like to use a stored procedure with parameters which creates and
> executes dynamic sql as a source for a report.
> I get an error, though, stating
> "could not generate a list of fields for the query".
> I tried adding the fields manually to the report definition and matching
> them with the fields in the dynamic SQL, but I received error messages like:
> "The value expression for the textbox 'blah' refers to the field 'blah'.
> Report item expressions can only refer to fields within the current data set
> scope. ..."
> I guess I can just put all the sql into the dataset directly, but it would
> be easier to manage in a stored procedure.
> It needs to be dynamic because I have to build a dynamic Order By clause
> with different alternative data types, so I can't use a Case statement.
> The stored procedure is something like this:
> Create Procedure sp
> @.param1 int,
> @.param2 int
> as
> declare @.sqlString (varchar(200))
> set @.sqlString => 'select col1, col2
> from tbl1
> where col1 = ' + @.param1 +
> 'col2 = ' + @.param2
> exec @.sqlString
> Thanks!
> Bill
>
>