Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
hohoan opened this issue Jun 7, 2024 · 6 comments

Comments

@hohoan
Copy link

hohoan commented Jun 7, 2024

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
Copy link
Member

mgravell commented Jun 7, 2024

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
Copy link
Author

hohoan commented Jun 11, 2024

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
Copy link

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
Copy link
Member

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
Copy link

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
Copy link

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?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

4 participants