Bug: Parameter not supplied error when executing stored procedure

[Simon-Carr]

Bug Description

When trying to execute a stored procedure I get a missing parameter exception. I have tried this with both the async and non-async methods and with both methods of executing a stored procedure. I have also tried various permutations of providing the ProviderID parameter.

  ids = await cnn.ExecuteQueryAsync<int>("exec dbo.s_API_Subscriptions_DiscountsForProvider(@ProviderID);", new {request.ProviderID},
                        CommandType.StoredProcedure, cancellationToken: cancellationToken);

 ids = cnn.ExecuteQuery<int>("dbo.s_API_Subscriptions_DiscountsForProvider", new {ProviderID=1234},
                        CommandType.StoredProcedure);

If I remove the parameter from the stored procedure and execute without passing any parameters it works 👍

ids = await cnn.ExecuteQueryAsync<int>("dbo.s_API_Subscriptions_DiscountsForProvider", commandType:
                        CommandType.StoredProcedure, cancellationToken: cancellationToken);

Exception Message:

Procedure or function 's_API_Subscriptions_DiscountsForProvider' expects parameter '@ProviderID', which was not supplied.

The stored procedure implementation is very simple:

CREATE OR ALTER PROCEDURE dbo.s_API_Subscriptions_DiscountsForProvider
	@ProviderID INT
AS
BEGIN

Library Version:

Example: RepoDb v1.12.7 and RepoDb.SqlServer v1.1.3

[mikependon]

Have you tried calling your SP like below?

ids = await cnn.ExecuteQueryAsync<int>("dbo.s_API_Subscriptions_DiscountsForProvider", new { request.ProviderID }, CommandType.StoredProcedure, cancellationToken: cancellationToken);

Notice, we have removed the EXEC and the ; character, and in addition to that, you do need to specify the SP parameters as you are passing the commandType.

If you want to use an EXEC approach execution, the commandType is not needed anymore and the code below should work.

ids = await cnn.ExecuteQueryAsync<int>("EXEC dbo.s_API_Subscriptions_DiscountsForProvider(@ProviderId);", new { request.ProviderID }, cancellationToken: cancellationToken);

You can also see our sample calls from the actual tutorial page. We also explain the importance of the ';' character during the calls. Hope this helps! 😄

[Simon-Carr]

Thanks for replying. Yes, I tried both ways, as per the tutorial page, but was unable to get it to work with a parameter. My second example is as per your first suggestion, although I have hardcoded the parameter in this case as a test.

[mikependon]

Okay. Sounds weird. Let me play around with it and maybe share the sample small solution here that does work for your reference.

[mikependon]

Here I created a simple solution for you using the latest version of the packages. I also tested the Async equivalent and it is working. Hope this helps.

RepoDbStoredProcedure.zip

[Simon-Carr]

Thanks for taking the time to do that. I can confirm that this indeed does work. The only differences here are the version of RepoDb that you are using and the fact that my app is .Net 5 (which perhaps I should have mentioned before).

As an aside, I would like to thank you for RepoDb. We are currently moving some of our stuff from Dapper/Dapper.Contrib to RepoDb as the ORM type features are far superior to Dapper.Contrib's, and the performance seems just as good as Dapper for the rest of it.

[mikependon]

Nice to know that it works on your side now and thanks for the nice word. :)