what 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 ...
[quoted text, click to view] "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
>
>
>
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
