Working with SSRS and a SQL 2000 source, I have a stored procedure that takes three parameters and returns a result set.
I have a dataset with the Command Type set to StoredProcedure and the procedure name in the Query String.
When I attempt to execute the procedure in Visual Studio/BIDS, the Define Query Parameters dialog box presents with each of the parameters from the procedure listed twice. Supplying values for the parameters and proceeding results in a too many arguments specified error.
How do I get SSRS to recognize and pass the correct number of parameters to the stored procedure?
I found a workaround for this problem:
Specify the procedure name and parameters:
EXEC procname @param1, @param2, @param3
"EXEC" and specifying the parameters are both requirements for it to work. The parameters can be named anything as long as they are prefixed with the "@" symbol. All parameters that require a value must be represented. Of course any optional parameters (those with defaults specified in the proc) must be represented in the command text if the report is going to reference them internally or present them as parameters for the user, but optional parameters do not have to be specified for the proc to run and return a result set.