Dapper stored procedure has too many arguments specified when passing IEnumerable to it

Question

I'm calling my procedure by this method:

public async Task<IEnumerable<Algorithm>> GetAlgorithmsByNameAsync(IEnumerable<string> names)
{
    var parameters = new DynamicParameters();
    parameters.Add("@names", names);

    var connection = _connection.GetOpenConnection();   
    return await connection.QueryAsync<Algorithm>("GetAlgorithmsByName", parameters, commandType: CommandType.StoredProcedure);
}

My Procedure looks like this:

CREATE TYPE [dbo].[StringList] AS TABLE(
    [Item] [NVARCHAR](MAX) NULL
);

--PROCEDURE HERE--

CREATE PROCEDURE GetAlgorithmsByName

@names StringList READONLY -- my own type

AS
BEGIN
    SELECT ALgorithmId, Name From Algorithms WHERE Name IN (SELECT Item FROM @names)
END

From the code above, I get an error:

"Procedure or function GetAlgorithmsByName has too many arguments specified."

What am I doing wrong? How do I pass IEnumerable<string> to a stored procedure using dapper?

Answer 1

In your stored procedure is expecting [Item] [NVARCHAR](MAX), it means one item Whereas you are passing IEnumerable<string> names. So that's the reason why you are getting the error.

There are numerous way to pass the list of string to sp

1. XML
2. Using table-valued parameters like CREATE TYPE NameList AS TABLE ( Name Varchar(100) );
3. Using names = "Name1, Name2, .. , Namen"; then sql you can use T-SQL split string to get the name list

---

Updated You are passing param incorrectly, Let's fix it by this way

     using (var table = new DataTable()) 
     {
      table.Columns.Add("Item", typeof(string));

      for (int i = 0; i < names.length; i++)
        table.Rows.Add(i.ToString());

      var pList = new SqlParameter("@names", SqlDbType.Structured);
      pList.TypeName = "dbo.StringList";
      pList.Value = table;

      parameters.Add(pList);
   }