Dapper: Procedure or function has too many arguments specified

Question

While using Dapper to call a stored procedure, I'm receiving the following error:

Procedure or function has too many arguments specified

I'm using DynamicParameters to add a list of simple parameters to the query.

The parameter code looks like this:

var parameters = new DynamicParameters();
parameters.Add(p.Name, p.Value, direction: p.Mode);

The query code looks like this:

var result = _connection.Query<T>(
            string.Format("{0}.{1}", request.SchemaName, request.StoredProcedureName),
            parameters,
            commandType: CommandType.StoredProcedure,
            transaction: _transaction);

The executing sql in the profiler shows as following:

exec dbo.storedProcedureName @ParameterNames1=N'ParameterName',@ParameterNames2=N'ParameterName',@RemoveUnused=1

@ParameterNames1 is not at all how the parameter is called. Actually, the names are being passed in as the values (N'ParameterName'). The @RemoveUnused parameter seems completely random to me, as it does not occur in the calling code at all.

The full code for this can be found here: GitHub project at lines 61 and 228.

Edit: I've found that the issue is caused by calling the same procedure twice, with different result sets. So the first time I'm calling it with Query, the second time with Query. Why Dapper is having trouble with this scenario is still a mystery.

Answer 1

I recently came across this issue and this appears to be caused by the following:

• Your stored procedure can return multiple datasets (maybe based on a condition parameter).
• You are calling the stored procedure using Query<T>() instead of QueryMultiple() and then mapping datasets via Read<T>.

We recently upgraded from an old version of Dapper to v1.4 in order to support Table Variable Parameters and we started experiencing this behaviour as a direct result of the upgrade.

Solution:

Convert your Query<T> based code to a QueryMultiple implementation.

Answer 2

I simply can't reproduce this:

public void SO25069578_DynamicParams_Procs()
{
    var parameters = new DynamicParameters();
    parameters.Add("foo", "bar");
    try { connection.Execute("drop proc SO25069578"); } catch { }
    connection.Execute("create proc SO25069578 @foo nvarchar(max) as select @foo as [X]");
    var tran = connection.BeginTransaction(); // gist used transaction; behaves the same either way, though
    var row = connection.Query<HazX>("SO25069578", parameters,
        commandType: CommandType.StoredProcedure, transaction: tran).Single();
    tran.Rollback();
    row.X.IsEqualTo("bar");
}
public class HazX
{
    public string X { get; set; }
}

works fine. There is a RemoveUnused property on DynamicParameters, bit: when using dynamic parameters, that shouldn't be added. I've even tried using the template based constructor:

parameters = new DynamicParameters(parameters);

but again: this works fine. Is it possible that you're using a really, really old version of dapper? What version are you using?