Jun 06, 2019 08:57 AM|PatriceSc|LINK
When using exec usp_module_assigned_details 221,1 how SQL Server could figured out you are passing @CountryID and @case ? It can only use the position so you are passing @CountryId and @ModuleId
IMO the confusion is caused by using parameters with the same names in your C# code but those parameters are to pass value from C# to the SQL statement that calls your SP and have no direct relation with how SP parameters are named. If you want to omit a
parameter with is not the last one you can run :
exec usp_module_assigned_details @CountryId=221,@Case=1
and so in your C# code you'll have :
What is before the = will be kept unchanged and is the name of the parameter in the SP. What is after the = is the value (and you could use other names if you want).
Note that you have a typo on @CountryId.
Edit: I'm not a big fan of this kind of design. Basically @case is to select a particular "branch" in your SP ie this is as if you transmit a part of the SP name as a data to single SP. I prefer to just use separate SPs. I see quite often issues caused by
a SP doing multiple things (which is the root cause for your issue, else you would likely just have two SP or more ach with just the single parameter you need).
At worst I would use @case at the first position and then a single @id parameter that would be used either as a @CountryId or as a @ModuleId depending on the case.