CODEWITHSUNDEEP
stored-proceduresparametersreportingservices-2005
John Mo
John Mo

Reputation: 1326

Stored procedure parameters in SSRS double up

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?

Upvotes: 1

Views: 5817

Answers (3)

CSmith
CSmith

Reputation: 11

So I know this is old but I did find a way to get to work and this is SQL Server 2000 (8.0.2039) Stored Procedure and SSRS Version 13.0.4224.16

I did with an expression:

="DECLARE @Month int; " & "DECLARE @Year int; " & "DECLARE @Format int; "  & "DECLARE @SQL nvarchar(4000); " & "SET @Month = " & CStr(Parameters!iMonth.Value) & " ; " & "SET @Year = " & CStr(Parameters!iYear.Value) & " ; " & "SET @Format = " & CStr(Parameters!iFormat.Value) & " ; " & "SET @SQL = 'EXEC [dbo].[sp_MyProc] @Month = @Month, @Year = @Year, @Format = @Format'; EXECUTE sp_executesql @SQL , N'@Month int, @Year int, @Format int ', @Month , @Year , @Format"

I tried almost everything else I found and it would not link the parameters to the report until I did this above for the Dataset defined by Query Type: Text and using the function expression.

Upvotes: 0

John Mo
John Mo

Reputation: 1326

I found a workaround for this problem:

  • Set the Command type to "Text"

  • 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.

Upvotes: 2

DForck42
DForck42

Reputation: 20357

check and make sure that you don't have extra parameters declared on the report, if you do delete the 2 extra and ensure that those are the ones being passed to the stored procedure.

also double check and make sure you didn't accidentally declare 4 parameters in the stored procedure.

Upvotes: 0

Related Questions