dynamic SQL stored procedure problem

Hi,
I have a project that all its reports are based on Dynamic SQL Stored Procedure. First, I had to handle the problem that in the Layout Tab there were no fields by adding them manually. However, when I try to show the report in the Preview tab I got the error: "Procedure x Expects a parameter @.y that was not Supplied." Despite the fact that I define the parameter in report parameter and in the Data Tab I got the correct SP answer.
When I wrote in the data set query "exec x @.y=1" and chose "Text" instead of "Stored Procedure" I got the correct SP answer, but because all the reports get their parameters from the application (user) I can not leave the report like that.
From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comYou should be able to do this:
exec x @.y and then map the query parameter to the report parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Avi" <savi176@.yahoo.com> wrote in message
news:1bda4f2c-19e6-4b8c-9bc9-c3d68cfcba8b@.developmentnow.com...
> Hi,
> I have a project that all its reports are based on Dynamic SQL Stored
> Procedure. First, I had to handle the problem that in the Layout Tab there
> were no fields by adding them manually. However, when I try to show the
> report in the Preview tab I got the error: "Procedure x Expects a
> parameter @.y that was not Supplied." Despite the fact that I define the
> parameter in report parameter and in the Data Tab I got the correct SP
> answer.
> When I wrote in the data set query "exec x @.y=1" and chose "Text"
> instead of "Stored Procedure" I got the correct SP answer, but because
> all the reports get their parameters from the application (user) I can
> not leave the report like that.
>
> From
> http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com|||Alot of times this will happen when you do not have the parameter defined in
the dataset. RS takes your parameters but ends up not passing them to the
stored proc because you haven't explicitly told it to in the properites of
the dataset. Check it by going to the appropriate dataset. hit the elipsis
(...)-> parameters tab. You need to list the parameters that the proc takes
in the correct order that it takes them. The name value list would look as
such
NAME VALUE
@.x =Parameters!X.value
@.y = Parameters!Y.Value
See if that helps!
Michael Abair
Programmer / Analyst
Chicos FAS Inc.
"Avi" <savi176@.yahoo.com> wrote in message
news:1bda4f2c-19e6-4b8c-9bc9-c3d68cfcba8b@.developmentnow.com...
> Hi,
> I have a project that all its reports are based on Dynamic SQL Stored
> Procedure. First, I had to handle the problem that in the Layout Tab there
> were no fields by adding them manually. However, when I try to show the
> report in the Preview tab I got the error: "Procedure x Expects a
> parameter @.y that was not Supplied." Despite the fact that I define the
> parameter in report parameter and in the Data Tab I got the correct SP
> answer.
> When I wrote in the data set query "exec x @.y=1" and chose "Text"
> instead of "Stored Procedure" I got the correct SP answer, but because
> all the reports get their parameters from the application (user) I can
> not leave the report like that.
>
> From
> http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com