ALTER PROC usp_module_assigned_details
(
@CounrtyID BIGINT = NULL,
@ModuleID BIGINT = NULL,
@case INT
)
AS BEGIN
IF @case = 1 -- Select assigned module to the country
BEGIN
SELECT ModuleID
FROM Country_Module_Assign
WHERE CountryID=@CounrtyID
END
END
I use this type style of store-procedure with ado.net and it works fine, without any issue. [I use more than 50 store procedure in my previous project. All of them work perfectly].
But when I try to use store-procedure with entity framework, it doesn't tale my variable (@ModuleID BIGINT = NULL), AS NULL. I already defined ion in my declaration part.
When I execute it by giving @CountryID and @case
exec usp_module_assigned_details 221,1
It says Procedure or function 'usp_module_assigned_details' expects parameter '@case', which was not supplied.
But when I execute like below
exec usp_module_assigned_details 221 ,NULL,1
It executes perfectly.
My question is that when I already defines @ModuleID BIGINT AS NULL, whey it is demanding it.
Below is my EF Code.
int country_id = Convert.ToInt32(Session["USER-Country"].ToString());
SqlParameter param1 = new SqlParameter("@CounrtyID", Convert.ToInt32(country_id));
SqlParameter param2 = new SqlParameter("@case", 1);
var ModuleList = db.Database.SqlQuery<ModuleModel>("usp_module_assigned_details @CounrtyID,@case", param1, param2).ToList();
ViewBag.AssignedModules = ModuleList;
return ModuleList;
Please Suggest.
It is our choices, that show what we truly are, far more than our abilities.
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 :
and so in your C# code you'll have :
SqlQuery<T>("usp_module_assigned_details @CountryId=@CounrtyID,@case=@case")
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.
You could just use the EF 6 backdoor using the IObjectcAdapter and just use the EF connection to execute the sproc using SQL Command Objects and ADO.NET traditionally, without EF being involved in the sproc execution.
If you find the post has answered your issue, then please mark post as 'answered'.
According to your description, if you set the default value to the parameter in the stored procedure, and the parameter is in front of the other parameters which are not set default value, then you need to assign the default parameter again.
That is, when assigning, the value should be consistent with the position of the parameter.
But If you don't want to assign values to default parameters, you could put the default parameters with default value at the end and parameters without default values at the front.
This way, when you execute stored procedures and assign values, you only need to assign parameters that do not set default values.
You could change your stored procedures like this:
ALTER PROC usp_module_assigned_details
(
@case INT ,
@CounrtyID BIGINT = NULL,
@ModuleID BIGINT = NULL
)
AS BEGIN
IF @case = 1 -- Select assigned module to the country
BEGIN
SELECT ModuleID
FROM Country_Module_Assign
WHERE CountryID=@CounrtyID
END
END
Best Regards,
YongQing.
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Do whatever works for you but here using a single SP handling two distinct @case and distinct parameters for each seems to have cause this issue...
Basically it seems you are using GetModule 'ByCountry',10 or GetModule 'ById',10 with a test to select the query in your single GetModule SP which is pretty similar to just calling GetModuleByCountry 10 or GetModuleById 10 and having a single statement in
each SP.
That is rather than having a single SP and passing a value to tell which variation you want, you just call directly the SP that contains the single statement you want.
That is rather than having a single SP and passing a value to tell which variation you want, you just call directly the SP that contains the single statement you want.
Thanks PatriceSc
It is our choices, that show what we truly are, far more than our abilities.
Participant
1359 Points
2647 Posts
After giving default value to NULL, store procedure still demand the variable.
Jun 06, 2019 07:46 AM|demoninside9|LINK
Hello Everyone,
Below is my Store-procedure.
ALTER PROC usp_module_assigned_details ( @CounrtyID BIGINT = NULL, @ModuleID BIGINT = NULL, @case INT ) AS BEGIN IF @case = 1 -- Select assigned module to the country BEGIN SELECT ModuleID FROM Country_Module_Assign WHERE CountryID=@CounrtyID END ENDI use this type style of store-procedure with ado.net and it works fine, without any issue. [I use more than 50 store procedure in my previous project. All of them work perfectly].
But when I try to use store-procedure with entity framework, it doesn't tale my variable (@ModuleID BIGINT = NULL), AS NULL. I already defined ion in my declaration part.
When I execute it by giving @CountryID and @case
It says Procedure or function 'usp_module_assigned_details' expects parameter '@case', which was not supplied.
But when I execute like below
It executes perfectly.
My question is that when I already defines @ModuleID BIGINT AS NULL, whey it is demanding it.
Below is my EF Code.
int country_id = Convert.ToInt32(Session["USER-Country"].ToString()); SqlParameter param1 = new SqlParameter("@CounrtyID", Convert.ToInt32(country_id)); SqlParameter param2 = new SqlParameter("@case", 1); var ModuleList = db.Database.SqlQuery<ModuleModel>("usp_module_assigned_details @CounrtyID,@case", param1, param2).ToList(); ViewBag.AssignedModules = ModuleList; return ModuleList;All-Star
44970 Points
16056 Posts
Re: After giving default value to NULL, store procedure still demand the variable.
Jun 06, 2019 08:57 AM|PatriceSc|LINK
Hi,
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 :
and so in your C# code you'll have :
SqlQuery<T>("usp_module_assigned_details @CountryId=@CounrtyID,@case=@case")
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.
Contributor
3651 Points
3124 Posts
Re: After giving default value to NULL, store procedure still demand the variable.
Jun 06, 2019 09:06 AM|DA924|LINK
Please Suggest.
You could just use the EF 6 backdoor using the IObjectcAdapter and just use the EF connection to execute the sproc using SQL Command Objects and ADO.NET traditionally, without EF being involved in the sproc execution.
Participant
920 Points
289 Posts
Re: After giving default value to NULL, store procedure still demand the variable.
Jun 06, 2019 09:22 AM|Yongqing Yu|LINK
Hi demoninside,
According to your description, if you set the default value to the parameter in the stored procedure, and the parameter is in front of the other parameters which are not set default value, then you need to assign the default parameter again.
That is, when assigning, the value should be consistent with the position of the parameter.
But If you don't want to assign values to default parameters, you could put the default parameters with default value at the end and parameters without default values at the front.
This way, when you execute stored procedures and assign values, you only need to assign parameters that do not set default values.
Specifically, you Could refer to this link:https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/specify-parameters?view=sql-server-2017#specifying-parameter-default-values
You could change your stored procedures like this:
ALTER PROC usp_module_assigned_details ( @case INT , @CounrtyID BIGINT = NULL, @ModuleID BIGINT = NULL ) AS BEGIN IF @case = 1 -- Select assigned module to the country BEGIN SELECT ModuleID FROM Country_Module_Assign WHERE CountryID=@CounrtyID END ENDBest Regards,
YongQing.
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Participant
1359 Points
2647 Posts
Re: After giving default value to NULL, store procedure still demand the variable.
Jun 06, 2019 09:35 AM|demoninside9|LINK
Yes, it is working perfectly.
Is there any issue using that type of design. I used it many times but did't find any difficulty. Even I find it to more maintainable.
Could you please elaborate a bit? I did not understand the sentence correctly.
All-Star
44970 Points
16056 Posts
Re: After giving default value to NULL, store procedure still demand the variable.
Jun 18, 2019 04:46 PM|PatriceSc|LINK
Do whatever works for you but here using a single SP handling two distinct @case and distinct parameters for each seems to have cause this issue...
Basically it seems you are using GetModule 'ByCountry',10 or GetModule 'ById',10 with a test to select the query in your single GetModule SP which is pretty similar to just calling GetModuleByCountry 10 or GetModuleById 10 and having a single statement in each SP.
That is rather than having a single SP and passing a value to tell which variation you want, you just call directly the SP that contains the single statement you want.
Participant
1359 Points
2647 Posts
Re: After giving default value to NULL, store procedure still demand the variable.
Jun 19, 2019 08:34 AM|demoninside9|LINK
Thanks PatriceSc