locked
store procedure don't give data with cross apply RRS feed

  • Question

  • User1052662409 posted

    Hi All,

    below is my store procedure.

    select  col as week_off from Country_Master cross apply(values ('Monday', Weekly_Off_Monday), ('Tuesday', Weekly_Off_Tuesday), ('Wednesday', Weekly_Off_Wednesday), ('Thursday', Weekly_Off_Thursday), ('Friday', Weekly_Off_Friday), ('Saturday', Weekly_Off_Saturday), ('Sunday', Weekly_Off_Sunday)) c (col, value) where bintid_pk=@country_id and value!=0;

    When I execute it in sql server it gives data like 

    Untitled

    I already execute many store procedure using entity framework and those executed successfully.

    But when I try to execute the above sp it gives 0 count in variable. I am doing like below.

      SqlParameter user_weekoff_param2 = new SqlParameter("@country_id", Convert.ToInt32(Session["USER-Country"].ToString()));
                            var user_weekoff = db.Database.SqlQuery<WeekOff>("usp_user_role_leave_details @country_id",  user_weekoff_param2).ToList();

    I guess here I am using cross apply (which I am using first time) this may be the reason or I need to modify the sp ?

    Please suggest.

    Thanks

    Wednesday, May 8, 2019 9:20 AM

All replies

  • User1724605321 posted

    Hi demoninsider9,

    Do you debug your application and make sure the `Convert.ToInt32(Session["USER-Country"].ToString()` get the expected value? You can also try SQL Server Profiler to capture the query to confirm whether it is the same .

    Best Regards,

    Nan Yu

    Thursday, May 9, 2019 2:35 AM
  • User1052662409 posted

    Convert.ToInt32(Session["USER-Country"].ToString()` get the expected value

    Yes I put the break point and by using quick watch I can see that this session variable  is having the right value.

    Thursday, May 9, 2019 3:27 AM
  • User1120430333 posted

    I guess here I am using cross apply (which I am using first time) this may be the reason or I need to modify the sp ?

    What is  'cross apply'?

    Thursday, May 9, 2019 12:01 PM
  • Thursday, May 9, 2019 12:09 PM
  • User753101303 posted

    Hi,

    So you mean testing your SP directly and from your C# with the same value doesn't give the same result set ? You are 100% sure you are testing with the same value on both sides ? Or you worked on two similar procedures and not testing the same one ?

    To start with the SP is giving the expected result ? If it does there is no reason for which you should change the SP just because it is called from C#.

    For now my understanding is that you are trying to call what appears the same procedure directly from SSMS or from C# and doesn't have the same result which usually happens because :
    - you don't really call the same procedure
    - or you mistakenly don't use the same database
    - triple check as well that you are using  really the same parameter value

    In some rare cases it could be also that something is done later that gives the impression you didn't get anything while it actually worked fine (for example put a breakpoint right away after the call and check if user_weekoff is empty rather than relying for example on UI code which could be where the problem is ?)

    Edit: for now I won't deal with using or not cross apply. I would do it likely this another way (basically you turn column into rows and I would just use rows from the start) but strictly speaking it is unrelated. If the result set you get is ok, you should have the same when calling this SP from #.

    Thursday, May 9, 2019 12:21 PM
  • User1052662409 posted

    In some rare cases it could be also that something is done later that gives the impression you didn't get anything while it actually worked fine (for example put a breakpoint right away after the call and check if user_weekoff is empty rather than relying for example on UI code which could be where the problem is ?)

    Sir, I am using the same database , same stored procedure with same data. I put break point and checked. for reference I am sharing the screen shots.

     1.Untitled2

    2.

    Untitled3

    3.

    Untitled

    Thursday, May 9, 2019 12:52 PM
  • User1120430333 posted

    https://www.c-sharpcorner.com/UploadFile/f0b2ed/cross-apply-and-outer-apply-in-sql-server/

    Thursday, May 9, 2019 1:13 PM
  • User753101303 posted

    Could it be that your SP returns multiple resultsets ? Show the full code for your SP. 

    If I really can't find what causes that my first move would be to start by returning a fixed result such as:

    SELECT 'Sunday' AS week_off

    with maybe a different hardcode value for each @case to see what happens.

    If ok I would add back my actual stuff in multiple steps to find out what is the change that starts causing this issue.
    If it still fails it would likely show that the issue is not on the SP side.

    In short if you don't see an obvious cause, the idea is to "question" your code even by doing actual changes to narrow down where it happens (here making 100% sure if this is a C# side or a DB side issue).

    Thursday, May 9, 2019 1:29 PM
  • User1052662409 posted

    PatriceSc

    Could it be that your SP returns multiple resultsets ?

    Yes.

    Below is my sp and I am talking about  for @Case =4

    ALTER PROC [dbo].[usp_user_role_leave_details] 
    (
     @Case INT,
     @bintId_Pk BIGINT=NULL ,
     @country_id BIGINT=NULL
    )
    AS BEGIN
    
    IF @Case = 1
    BEGIN
    
        SELECT dateofend AS end_date,ISNULL(accountlockdate,GETDATE()-120) AS account_lock_date FROM AdminMaster_GEN WHERE bintId_Pk=@bintId_Pk -- here @bintId_Pk is Session["AdminID"]
    
    END
    
    IF @Case = 2
    
    BEGIN
      
       SELECT bigid AS id,projectpart AS project_part,Role As role FROM Role_Master WHERE bigid in (SELECT teamid FROM AdminMaster_GEN WHERE bintId_Pk=@bintId_Pk)
    END
    
    IF @Case = 3
    BEGIN
    
      SELECT *,(SELECT sickleavedays FROM Country_Master cm WHERE cm.bintid_pk=a.CountryID) AS sick_leave_days,(SELECT leaveapproval FROM Country_Master cm WHERE cm.bintid_pk=a.CountryID) AS leave_approval,isnull((SELECT leave_half FROM Country_Master cm WHERE cm.bintid_pk=a.CountryID),0) AS half_day,ISNULL((SELECT leave_Request_Approval FROM Country_Master cm WHERE cm.bintid_pk=a.CountryID),0) AS leave_rerquest,ISNULL((SELECT Invoice_Step_Email FROM Country_Master cm WHERE cm.bintid_pk=a.CountryID),0) AS invoice_step_email,(SELECT currency FROM country_master cm WHERE cm.bintid_pk=a.CountryID) AS currency,(SELECT ISNULL(salutation,'')+' '+vcFirstName+' '+vclastname FROM AdminMaster_GEN cm WHERE cm.bintID_pk=a.supervisor) AS supervisor_name,(SELECT vctitle FROM Country_Master cm WHERE cm.bintID_pk=a.CountryID) AS country_name,ISNULL((SELECT Request_Role FROM Country_Master cm WHERE cm.bintID_pk=a.CountryID),0) AS request_role,ISNULL((SELECT Invoice_Role FROM Country_Master cm WHERE cm.bintID_pk=a.CountryID),0) AS invoice_role,isnull((SELECT leaveconsider4days FROM Country_Master cm WHERE cm.bintid_pk=a.CountryID),0) AS friday_to_monday_4_days ,a.CountryID AS country_id, a.vcphoto AS user_photo,a.vcprojectnumber AS project_number ,a.vcMobile AS mobile, a.skypeid AS skype_id ,a.salutation AS salutation, a.SAPno AS sap_no,a.emptype AS emp_type  FROM AdminMaster_GEN a WHERE bintId_Pk=@bintId_Pk-- here @bintId_Pk is Session["AdminID"]
      END
    
    IF @Case =4 -- to get weekly off
    BEGIN
    
    select  col as week_off from Country_Master cross apply(values ('Monday', Weekly_Off_Monday), ('Tuesday', Weekly_Off_Tuesday), ('Wednesday', Weekly_Off_Wednesday), ('Thursday', Weekly_Off_Thursday), ('Friday', Weekly_Off_Friday), ('Saturday', Weekly_Off_Saturday), ('Sunday', Weekly_Off_Sunday)) c (col, value) where bintid_pk=@country_id and value!=0;
    
    END
    
    
    END

    Below is model class

     public class WeekOff
        {
            public string week_off { get; set; }
        }

    and the execution of sp is below

                            SqlParameter user_weekoff_param1 = new SqlParameter("@Case", 4);
                            SqlParameter user_weekoff_param2 = new SqlParameter("@country_id", Convert.ToInt32(Session["USER-Country"].ToString()));
                            var user_weekoff = db.Database.SqlQuery<WeekOff>("usp_user_role_leave_details @Case,@country_id", user_weekoff_param1, user_weekoff_param2).ToList();

    Friday, May 10, 2019 3:12 AM
  • User753101303 posted

    Ah what if you delete parameters from your C# SP call ie just use the SP name :

    ….SqlQuery<WeekOff>("usp_user_role_leave_details", etc...)

    It should then map directly the parameters you provided to those expected by your SP. For now you are using parameters with the same name in your SQL statement so you end up in calling your SP using :

    usp_user_role_leave_details 4,221 (and so 221 is your 2rd and not rd parameter) rather than

    usp_user_role_leave_details 4,NULL,221

    Friday, May 10, 2019 8:04 AM
  • User1052662409 posted

    Ah what if you delete parameters from your C# SP call ie just use the SP name :

    ….SqlQuery<WeekOff>("usp_user_role_leave_details", etc...)

    Procedure or function 'usp_user_role_leave_details' expects parameter '@Case', which was not supplied.'

    Friday, May 10, 2019 10:18 AM
  • User1052662409 posted

    PatriceSc

    usp_user_role_leave_details 4,NULL,221

    exec usp_user_role_leave_details 4,NULL,221

    yes the above line gives results in sql but not in C# code

    But when I change the parameters order

    from

    (
     @Case INT,
     @bintId_Pk BIGINT=NULL ,
     @country_id BIGINT=NULL
    )

    To

    (
     @Case INT,
    @country_id BIGINT=NULL,
    @bintId_Pk BIGINT=NULL )

    Then the procedure gives result but the other in other case (@Case = 3) it gives 0 count. 

    and the below code is working fine

    SqlParameter user_weekoff_param1 = new SqlParameter("@Case", 4);
                            SqlParameter user_weekoff_param3 = new SqlParameter("@bintId_Pk", DBNull.Value);
                            SqlParameter user_weekoff_param2 = new SqlParameter("@country_id", Convert.ToInt32( Session["USER-Country"].ToString()));
                            var user_weekoff = db.Database.SqlQuery<WeekOff>("usp_user_role_leave_details @Case,@bintId_Pk, @country_id", user_weekoff_param1, user_weekoff_param3, user_weekoff_param2).ToList();

    But I am wondering and I never assume that here I require @bintId_Pk as I already set it to NULL at initial stage. 

    How it can be possible that you procedure expects a parameter which you already set to NULL

    I used this approach many times but my sp never expected a parameter which I set to NULL at declaration point.

    (and more important part that @Case 3 doesn't require (manually) @country_id as NULL it automatically ignore it as @county_id is NULL at declaration point)

    Any comments?

    Friday, May 10, 2019 10:24 AM
  • User753101303 posted

    IMO technically @country_id in your C# code is NOT the parameter of your SP but the parameter of the SQL statement that calls this SP. This parameter is at position 2 and then end up in your @bintId_Pk parameter while @country_id is left to NULL.

    The best way to understand this would be to use SQL Server Profiler to see what happens under the hood.

    For example this sample shows first a wrong output (uses just the position so @c ends up at the wrong place) and the correct output as @c=@c allows to map the parameter named @c inside the SQL startement to the parameter named @c inside the SP.

    CREATE PROCEDURE test(@a INT,@b INT=null,@c INT=NULL)
    AS SELECT @a,@b,@c
    GO
    EXEC sp_executesql N'test @a,@c',      N'@a INT,@c INT',@a=1,@c=3 -- 1,3,NULL
    EXEC sp_executesql N'test @a=@a,@c=@c',N'@a INT,@c INT',@a=1,@c=3 -- 1,NULL,3

    Edit : seems you never use both parameters so @case and a single @id parameter (used differently depending on the @case) could be enough. I also dislike having a single SP doing different things depending on a parameter (basically @case acts as if it was actually a part of the sp name as it is used to ultimately know what you really want to call).

    Friday, May 10, 2019 12:38 PM