Issue with Dapper: Intermittent '@ParameterNames1 is not a parameter for procedure...' Error

[hohoan]

Hello,

I'm encountering an intermittent issue with Dapper. Occasionally, I receive the error message '@ParameterNames1 is not a parameter for procedure....' When I stop and restart the application using 'dotnet run', the error disappears, and everything works fine. However, after a while, the error reappears.

I've thoroughly checked the parameters I'm passing, and they are correct because the application runs without issues after restarting.

Could you please help me troubleshoot this issue?

Thank you.

P/s: Dapper 2.1.44 and .Net 8.0

``

        using var con = new SqlConnection(_connectionString);
        const string sql = "TestStore";
        DynamicParameters para = new();
        para.Add("@type", 1);
        para.Add("@employeeID", employeeID);

        var result = await con.QueryAsync<object>(sql, para, commandType: CommandType.StoredProcedure);

[mgravell]

Does any part of your code use something akin to ParameterNames ? Also: note that DynamicParameters is not needed here - a new { type = 1, employeeId } would be more efficient

[hohoan]

Thanks for replying.

I used 'new {type = 1, employeeId}' and didn't get that error. I'm continuing to monitor to see if there are any other issues.

[Wintekso]

Greetings,

First off thank you dapper team for providing this library.

We have also experienced this when using QueryAsync against a stored procedure and dynamic parameters passed. The sql profiler shows the correct exec sp_test @Id = 'guid'

Until randomly it will show exec sp_test @ParameterNames1=N'Id' etc which causes the failure.

For anyone else experiencing the specific issue (@ParameterNames1 is not a parameter for procedure), we found the QueryMultiple functions work every time so we have moved to using this with stored procedures returning results.

example of changing to querymultiple:

    var result = await connection.QueryAsync<TDbItem>(StoredProcedureName, parameters, commandType: CommandType.StoredProcedure);
    return result;

to

    var queryResult = await connection.QueryMultipleAsync(StoredProcedureName, parameters, commandType: CommandType.StoredProcedure);
    return await queryResult.ReadAsync<TDbItem>();

Hope that helps

Many thanks

[mgravell]

This sounds very odd; I wonder whether there is some kind of internal strategy cache conflict happening here. I'll take a look at that, but as a side note: you could try using AOT mode - there is no strategy cache in the AOT implementation, so: nothing to get conflicted on; you can try this via: https://aot.dapperlib.dev/gettingstarted.html

[TiagoAntunesALS]

We had the same issue today. We also use DynamicParameters. We have two methods calling the same procedure: one is using QueryFirstOrDefaultAsync, and the other is using QueryMultipleAsync.

The procedure is something like this:

CREATE OR ALTER PROCEDURE [dbo].[GetNames](
    @Ids dbo.Ids_Type READONLY
    , @ParentId UNIQUEIDENTIFIER = NULL
)
AS
BEGIN

    SELECT
        main.Id
        , main.Name
    FROM @Ids ids
    JOIN [Table2] main on main.Id = ids.Id
    
    IF @ParentId IS NULL RETURN

    SELECT TOP 1
        main.Id
        , main.Name
    FROM [Table1] main
    WHERE main.Id = @ParentId
   
END;
GO

[TiagoAntunesALS]

Does any part of your code use something akin to ParameterNames ? Also: note that DynamicParameters is not needed here - a new { type = 1, employeeId } would be more efficient

I always thought that DynamicParameters would be more efficient than using anonymous types. Is this always the case?

[DHCFTAlexSmith]

Any news on this?

I've recently started experiencing this with our move to Dapper. When I'm calling a specific stored proc using QueryAsync, Query, QueryMultipleAsync (doesn't matter which), no matter what I call the last parameter, we get "@(parametername)1 is not a parameter for procedure (procedurename)."

Funnily enough, I've only had it when calling the proc from within a Razor Page PageModel class.

A brand new connection is instantiated before each call, tried parameters passed in as DynamicParameters and ExpandoObject (because I build them on the fly). I'm definitely not passing in the problem parameter my end.

We're on .NET 8 and Dapper 2.1.66

[mgravell]

I always thought that DynamicParameters would be more efficient than using anonymous types. Is this always the case?

Other way around; DynamicParameters is the expensive one

Any news on this?

I don't have a repro; do you have a repro?

Note that if this is a cache collision problem: Dapper.AOT will resolve it, because Dapper.AOT doesn't use a cache/lookup - it goes directly to the bits emitted at build-time.