Parameter not provided to the stored procedure when I use Dapper DynamicParameter from C# dictionary

Question

I'm using Dapper DynamicParameters in order to send a parameters dictionary from my .NET Core application to SQL Server, but I get an error:

Parameter @cityName1 is not provided

But when I trace the query, I can see it is present.

This is the code :

Fill in the dictionary:

Dictionary<string, object> cities = new Dictionary<string, object>();
cities.Add("@cityName1", "New-York");
cities.Add("@cityId1", 123);

The call to Dapper:

DynamicParameters dbArgs = new DynamicParameters();

foreach (var pair in cities)
{
   dbArgs.Add(pair.Key, pair.Value);
}

return await connection.QueryAsync<Provider>(SQL_GET_LINKS_BY_CITIES, dbArgs);

The stored procedure:

CREATE PROCEDURE [dbo].[hp_GetLinksByCities]
    (@cityName1 NVARCHAR(25),
     @cityId1 INT)
AS
BEGIN
    -- code
END

The trace from the profiler:

exec sp_executesql N'[dbo].[hp_GetLinksByCities]',N'@cityName1 nvarchar(4000),@cityId1 int',@cityName1=N'Marseille',@cityId1=1970

The error

Original (in French):

La procédure ou fonction 'hp_GetLinksByCities' attend le paramètre '@cityName1', qui n'a pas été fourni.

Translated to English:

The stored procedure or function 'hp_GetLinksByCities' is expecting a '@cityName1' parameter which was not supplied

When I executed this code in SSMS, I got the same error, but with this syntax, it's working fine :

exec [dbo].[hp_sp_GetDirectoryPageLinksByCities] @cityName1=N'Marseille',@cityId1=1970

Usually, Dapper sends order that way and It's working very well. Why does it act differently this time ?

I have no clue of what's wrong... do I need to use the DynamicParameter differently?

Thanks for any help :)

`exec sp_executesql N'[dbo].[hp_GetLinksByCities]', ...` isn't passing the parameters to the proc though, just to `sp_executesql`. (Note I don't know how to fix that, I know nothing about Dapper, just explaining why the error occurs.) — Thom A, Apr 08 '21 at 09:52
I'm a little confused - the proc takes 2 parameters only, but you seem to be adding lots in a loop - in the general way, that won't work; you'd need an execute per pair, and if you're doing an execute per pair, you don't need dynamic args at all. Random thing to try: take the `@` out of the string, i.e. `cities.Add("cityName1", "New-York");` etc — Marc Gravell, Apr 08 '21 at 09:53
@MarcGravell Actually the real SP accepts 25 parameters x2. I simplified the code here but I forgot to remove the foreach line ;) — ordiminnie, Apr 08 '21 at 10:26

score 3 · Accepted Answer · answered Apr 08 '21 at 09:54

You're missing commandType: CommandType.StoredProcedure in the call to Dapper, so it is issuing it as a vanilla text query, which means it is issuing just hp_GetLinksByCities by itself as a raw command, which doesn't pass down the args.

Note: we've discussed interpreting single word commands as stored procedures automatically, but to date: we haven't made that change - so the command-type needs to be specified explicitly.

Parameter not provided to the stored procedure when I use Dapper DynamicParameter from C# dictionary

1 Answers1