After giving default value to NULL, store procedure still demand the variable. [Answered] RSS

• • Reply
  demoninside9

  Participant

  1448 Points

  2845 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
  
  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.
  
• • Reply
  PatriceSc

  All-Star

  48720 Points

  18186 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 :

  exec usp_module_assigned_details @CountryId=221,@Case=1

  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.

• • Reply
  DA924

  Contributor

  4973 Points

  4264 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.

  If you find the post has answered your issue, then please mark post as 'answered'.
• • Reply
  Yongqing Yu

  Contributor

  3720 Points

  1043 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
  
  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.
• • Reply
  demoninside9

  Participant

  1448 Points

  2845 Posts

  Re: After giving default value to NULL, store procedure still demand the variable.

  Jun 06, 2019 09:35 AM|demoninside9|LINK

  PatriceSc

  SqlQuery<T>("usp_module_assigned_details @CountryId=@CounrtyID,@case=@case")

  Yes, it is working perfectly.

  PatriceSc

  I'm not a big fan of this kind of design

  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. 

  PatriceSc

  this is as if you transmit a part of the SP name as a data to single SP

  Could you please elaborate a bit? I did not understand the sentence correctly.

  It is our choices, that show what we truly are, far more than our abilities.
  
• • Reply
  PatriceSc

  All-Star

  48720 Points

  18186 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.

• • Reply
  demoninside9

  Participant

  1448 Points

  2845 Posts

  Re: After giving default value to NULL, store procedure still demand the variable.

  Jun 19, 2019 08:34 AM|demoninside9|LINK

  PatriceSc

  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.