locked
After giving default value to NULL, store procedure still demand the variable. RRS feed

  • Question

  • User1052662409 posted

    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.

    Thursday, June 6, 2019 7:46 AM

Answers

  • User753101303 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 6, 2019 8:57 AM
  • User665608656 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 6, 2019 9:22 AM

All replies

  • User753101303 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 6, 2019 8:57 AM
  • User1120430333 posted

    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.

    Thursday, June 6, 2019 9:06 AM
  • User665608656 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 6, 2019 9:22 AM
  • User1052662409 posted

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

    Yes, it is working perfectly.

    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. 

    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.

    Thursday, June 6, 2019 9:35 AM
  • User753101303 posted

    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.

    Tuesday, June 18, 2019 4:46 PM
  • User1052662409 posted

    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 

    Wednesday, June 19, 2019 8:34 AM