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?

Marc Gravell · Answer 1 · 2020-01-12T10:37:54.470

Table valued parameters aren't trivial to use; one way is via the extension method that Dapper adds on DataTable (something like AsTableValuedParameter), but: it doesn't work as simply as IEnumerable<T> - at least, not today. You also probably don't need DynamicParameters here.

If what you want is just a set of strings, then one very pragmatic option is to look at the inbuilt string_split API in SQL Server, if you can define a separator token that is never used in the data. Then you can just pass a single delimited string.

Nguyễn Văn Phong · Accepted Answer · 2020-01-12T10:27:37.697

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

XML
Using table-valued parameters like CREATE TYPE NameList AS TABLE ( Name Varchar(100) );
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);
   }

edited Jan 12 '20 at 10:27

answered Jan 12 '20 at 09:57

Nguyễn Văn Phong

13,506
17
39
56

2

Your second option is what OP has in the question – Marc Gravell Jan 12 '20 at 10:03
Oops, sorry my bad. Let me updated my answer. Thanks sir. – Nguyễn Văn Phong Jan 12 '20 at 10:20
I've just updated my answer, please take a look @Marc Gravell♦ However, In my opinion, the params is quite simple, OP no need to create `table-valued parameters `, should use option 3 instead. – Nguyễn Văn Phong Jan 12 '20 at 10:32
2

The code at the end seems to ignore the fact that OP is using dapper, not raw ADO.NET; the data-table bit looks fine, but there's an extension method - something like AsTableValuedParameter that is used with Dapper – Marc Gravell Jan 12 '20 at 10:37
1

Thanks for your advices! I will check everything soon when i will be back to my project :) – michasaucer Jan 12 '20 at 16:29
@Anonymous i like the solution with spliting string (3. paragraph) because its looks simplest. Can you tell me, how to deal with this function in procedure? Do i need to `DECLARE @value` that attach to that value strings, something like `DECLARE @names = SELECT * FROM dbo.split('1,2,3')` and then i will query from `@names` like from table? What type `@names` should be? – michasaucer Jan 13 '20 at 06:42
type `@names` should be `NVARCHAR(MAX)`. As you can see, it returns `table` instead of `string`. So you can join to get value accordingly – Nguyễn Văn Phong Jan 13 '20 at 06:44

score 0 · Answer 3 · answered Jan 12 '20 at 10:54

0

You can use the IEnumerable (dynamic) rather than IEnumerable (string).

Check this link and try How to Implement IEnumerable (dynamic)

answered Jan 12 '20 at 10:54

ali

175
1
4
21

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

3 Answers3