Dynamic SQL Stored Precedure problem

Hi,

I have a project that all its reports are based on Dynamic SQL Stored Procedures. 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 setquery"execx @.y=1"and chose "Text" instead of"Stored Procedure"I got thecorrect SP answer, but because all the reports gettheir parameters from the application (user)I can not leave the report like that.

Try this: As a data set write static query that returns all the fields of your dynamic query and uses all the parameters. Then refresh report so it will update parameter list and field names. After that you can change static query to dynamic.

I did it like this:

declare @.sql varchar(max); set @.sql = '';

set @.sql = '.....'

exec (@.sql)

Maciej