Problem with using report parameters in SQL query function, SSRS 2

Using Visual Studio to build reports for Reporting Services 2000: is it
allowed to pass a report parameter to a FUNCTION in dataset defintion. Like:
select function(@.variable) from table where ..
I get "syntax error or access violation" when trying.
Regards, JouniI think that question would be more appropriate in the sqlserver.programming
group. With that said...
Have you tried running that query in Query Analyzer?
yeah...I don't think that Sql statement will ever run.
from the Books Online for CREATE FUNCTION (transact SQL):
"@.parameter_name
Is a parameter in the user-defined function. One or more parameters can be
declared.
[...]
Specify a parameter name by using an at sign (@.) as the first character. The
parameter name must comply with the rules for identifiers. Parameters are
local to the function; the same parameter names can be used in other
functions. Parameters can take the place only of constants; they cannot be
used instead of table names, column names, or the names of other database
objects. "
Soooo...assuming your user-defined function is a scalar-value function that
returns field names and is owned by "dbo" schema, try using dynamic sql,
something like this:
DECLARE @.sql varchar(8000)
SET @.sql= 'SELECT ' + dbo.myFn(@.myParam) + ' AS MyDynamicColumn '
SET @.sql= @.sql + ' FROM MyTable '
SET @.sql= @.sql + ' WHERE ... '
EXEC @.sql
Give it a shot, and if I am way off...reply and let me know. Hope this help
you.
--
Regards,
Thiago Silva
"Jouni" wrote:
> Using Visual Studio to build reports for Reporting Services 2000: is it
> allowed to pass a report parameter to a FUNCTION in dataset defintion. Like:
> select function(@.variable) from table where ..
> I get "syntax error or access violation" when trying.
> Regards, Jouni|||Hi Thiago,
haven't had time to test this. Actually I already switched to another
approach: defining the whole query as a db procedure and just calling that:
exec jounis_procedure @.report_parameter
I noticed that Microsoft people have chosen this approach themselves: in
"Report Pack for SPS" all the reports use procedure calls.
Regards,
Jouni
"HC" wrote:
> I think that question would be more appropriate in the sqlserver.programming
> group. With that said...
> Have you tried running that query in Query Analyzer?
> yeah...I don't think that Sql statement will ever run.
> from the Books Online for CREATE FUNCTION (transact SQL):
> "@.parameter_name
> Is a parameter in the user-defined function. One or more parameters can be
> declared.
> [...]
> Specify a parameter name by using an at sign (@.) as the first character. The
> parameter name must comply with the rules for identifiers. Parameters are
> local to the function; the same parameter names can be used in other
> functions. Parameters can take the place only of constants; they cannot be
> used instead of table names, column names, or the names of other database
> objects. "
> Soooo...assuming your user-defined function is a scalar-value function that
> returns field names and is owned by "dbo" schema, try using dynamic sql,
> something like this:
> DECLARE @.sql varchar(8000)
> SET @.sql= 'SELECT ' + dbo.myFn(@.myParam) + ' AS MyDynamicColumn '
> SET @.sql= @.sql + ' FROM MyTable '
> SET @.sql= @.sql + ' WHERE ... '
> EXEC @.sql
> Give it a shot, and if I am way off...reply and let me know. Hope this help
> you.
> --
> Regards,
> Thiago Silva
> "Jouni" wrote:
> > Using Visual Studio to build reports for Reporting Services 2000: is it
> > allowed to pass a report parameter to a FUNCTION in dataset defintion. Like:
> >
> > select function(@.variable) from table where ..
> >
> > I get "syntax error or access violation" when trying.
> >
> > Regards, Jouni