Thursday, March 7, 2019

C# SqlException: Procedure or function has too many arguments specified

By Steve Endow

I've been fighting this error for years in my Data Access class. Only yesterday did I figure out the cause.

It's related to the design of my Data Access class, and specific cases where I need to make multiple calls to the database in a single method.

This is the error:

SqlException: Procedure or function has too many arguments specified

This is an example of a stored procedure call that leads up to the error.

Assembling SQL Parameters for a Stored Procedure

I setup a SQL parameter array, populate the array, and send that array off to my ExecuteProc method, which in turn calls ExecuteNonQuery().

Nothing fancy.

So, that first call to execute the "zDP_CO00101SI" procedure works just fine.  No issues.  Just peachy.

But, right after calling the CO00101SI procedure, I need to call a second stored proc.

Just Another Stored Procedure Call

The first procedure executes fine, but this second procedure throws the SqlException, complaining that I'm sending in too many arguments.

Huh.  That's odd.

I double check the array size.  I re-count the number of parameters.  I make sure that I'm not somehow accidentally assigning extra values to the array.  But everything looks fine.

Before setting the values for the SQL Parameter array, I'm re-declaring the array, which resets the array size and clears out any prior values.

So the array shouldn't persist between calls, and shouldn't retain its prior size or values.

What is going on?

I pull out the call for zDP_CO00102SI into a separate test method so that only that one procedure is called, and I copy the same code with the same parameters into the test method.

That test method runs just fine.  No error.  No "too many arguments" error.

So my code is fine.  And I can call the second stored procedure.

But I can't call the first stored procedure followed by the second stored procedure.

What in the world is going on?

After Googling a half a dozen terms, I finally stumble on a thread that doesn't tell the person to make sure that their parameter count matches their stored procedure. Someone suggests checking to see if they have cleared their SQL Command Parameters.

Say what?

As I review my code to see what this may be referring to, the problem is slowly bubbling up in my hind brain that thinks in terms of code.

The second I look at my code, I immediately see the problem.

Well THERE'S your problem!

The error has nothing to do with my SQL Parameters arrays.

The problem is that I am re-using my SQL Command object for multiple calls in order to maintain a SQL Transaction.

And as you can see in my ExecuteProc code, I have this line:


So when I call the first stored procedure, it adds 14 parameters to the SQL Command object.

And when I call the second stored procedure, that adds an additional 11 parameters to the same SQL Command object.

When the second stored procedure is executed, the SQL Command object is sending 25 parameters to SQL Server.  And of course SQL Server is correctly complaining that is too many parameters for the procedure.


So how do you fix this?

You add a single line of code to clear the SQL Command Parameters after each command is executed.

A simple fix for a subtle issue

And once I cleared the parameters for the SQL Command object after every command execution. the code worked just fine.  I was able to call multiple stored procedures in a single SQL transaction without any errors.

I won't say that it is obvious, even in hindsight, but it makes complete sense now that I've figured it out.

And with that, one more bug has been slain in my code.

You can also find him on Twitter and YouTube

1 comment:

All comments must be reviewed and approved before being published. Your comment will not appear immediately.

How many digits can a Business Central Amount field actually support?

 by Steve Endow (If anyone has a technical explanation for the discrepancy between the Docs and the BC behavior, let me know!) On Sunday nig...