Data retrieved from T-SQL stored procedure into C# DataTable are skewed

Question

I do have a strange problem I really do not understand when I retrieve data from a T-SQL stored procedure into a C# DataTable. The result I get in T-SQL is different from the result the DataTable shown after the data has been retrieved. Different data means that cells in the DataTable are either missing rows, or showing different data.

Here is the code I am using in C#

public DataTable  GetData()
{
    string nameOfSp = "sp_SIA_Get_Ratio_Demand_Receipt";

    DataAccess dataAccess = new DataAccess();

    DataTable dt = new DataTable();

    dataAccess.OpenDatabase(DataAccess.ConnectionTo.SQL);

    SqlConnection sqlCon = dataAccess.GetSqlConnection();

    try
    {
        SqlCommand cmd = new SqlCommand(nameOfSp, sqlCon);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandTimeout = 1000;

        cmd.Parameters.Add("@COMPANY", SqlDbType.VarChar).Value = filter.CompanyNo;
        cmd.Parameters.Add("@YEARWEEK_FROM", SqlDbType.VarChar).Value = filter.YearWeekFrom;
        cmd.Parameters.Add("@YEARWEEK_TO", SqlDbType.VarChar).Value = filter.YearWeekTo;
        cmd.Parameters.Add("@USERNAME", SqlDbType.VarChar).Value = filter.UserName;

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(dt);
    }
    catch (Exception ex)
    {
        MessageBox.Show("Could not retrieve data from stored procedure " + ex.Message);
    }

    dataAccess.CloseDatabase();

    return dt;
}

And here is the SQL statement:

ALTER PROCEDURE [dbo].[sp_SIA_Get_Ratio_Demand_Receipt]
    @COMPANY as varchar(4),
    @USERNAME as varchar(50),
    @YEARWEEK_FROM as varchar(6),
    @YEARWEEK_TO as varchar(6)
AS
BEGIN
    SET NOCOUNT ON;

    WITH #MATERIAL (COMPANY_NO, MATERIAL_NO, STORAGE_LOCATION, INVENTORY_QTY, INVENTORY_VAL) AS 
    (
        SELECT 
            p.COMPANY_NO, p.ITEM_NO, p.STORAGE_LOCATION, 
            SUM(STOCK_QUANTITY), SUM(STOCK_VALUE)
        FROM  
            tbl_SIA_Sel_PartNos p 
        INNER JOIN 
            [FUM_DWH].[dbo].[View_SCM_FACT_610_WAREHOUSE_STOCK] s ON p.ITEM_NO = s.ITEM_NO
                                                                  AND p.COMPANY_NO = s.COMPANY_NO
                                                                  AND p.STORAGE_LOCATION = s.STORAGE_LOCATION
        WHERE 
            p.USERNAME = @USERNAME
            AND s.YEARWEEK =@YEARWEEK_TO
            AND p.ITEM_NO IN( '50048348') --p.ITEM_NO IN( '50057896','50048348','50293653','50059293')
        GROUP BY 
            p.COMPANY_NO, p.ITEM_NO, p.STORAGE_LOCATION
    )
        
    ,#DEMAND (COMPANY_NO, MATERIAL_NO, STORAGE_LOCATION, DEMAND_QTY) AS
    (
        SELECT  
            STRI_COMPANY_NO,
            STRI_ITEM_NO,
            STRI_STORAGE_LOCATION,
            SUM(STRI_QUANTITY * -1) AS DEMAND_QTY
        FROM 
            #MATERIAL m 
        INNER JOIN 
            [FUM_DWH].[dbo].[View_SCM_FACT_620_PAST_STOCK_RECEIPT_ISSUE] p ON m.MATERIAL_NO = p.STRI_ITEM_NO 
                                               AND p.STRI_STORAGE_LOCATION = m.STORAGE_LOCATION
        WHERE 
            STRI_COMPANY_NO = @COMPANY 
            AND CONCAT(STRI_YEAR, FORMAT( STRI_WEEK, '00')) BETWEEN @YEARWEEK_FROM AND @YEARWEEK_TO
            AND STRI_DEBIT_CREDIT = 'H' --nur Abgänge werten
        GROUP BY 
            STRI_ITEM_NO, STRI_COMPANY_NO, STRI_STORAGE_LOCATION
    )
    , #RECEIPT(COMPANY_NO, MATERIAL_NO, STORAGE_LOCATION, RECEIPT_QTY) AS
    (
        SELECT  
            STRI_COMPANY_NO,
            STRI_ITEM_NO,
            STRI_STORAGE_LOCATION,
            SUM(STRI_QUANTITY) AS RECEIPT_QTY
        FROM 
            [FUM_DWH].[dbo].[View_SCM_FACT_620_PAST_STOCK_RECEIPT_ISSUE] p 
        INNER JOIN 
            #MATERIAL m ON p.STRI_ITEM_NO = m.MATERIAL_NO 
                        AND p.STRI_STORAGE_LOCATION = m.STORAGE_LOCATION
        WHERE 
            STRI_COMPANY_NO = @COMPANY
            AND CONCAT(STRI_YEAR, FORMAT(STRI_WEEK, '00')) BETWEEN      @YEARWEEK_FROM AND @YEARWEEK_TO
            AND STRI_DEBIT_CREDIT = 'S' --nur Zugänge werten 
        GROUP BY 
            STRI_ITEM_NO, STRI_COMPANY_NO,STRI_STORAGE_LOCATION
    )
    ,#DEMAND_FUT (MATERIAL_NO, STORAGE_LOCATION, DEMAND_QTY, DEMAND_VAL) AS 
    (
        SELECT 
            [ITEM_NO], s.STORAGE_LOCATION,
            SUM([QUANTITY]) * -1 AS DEMAND_QTY,
            SUM([VALUE]) * -1 AS DEMAND_VAL
        FROM 
            [FUM_DWH].[dbo].[View_SCM_FACT_630_FUTURE_STOCK_RECEIPT_ISSUE] s 
        INNER JOIN 
            #MATERIAL m ON m.MATERIAL_NO = s.ITEM_NO 
                        AND m.STORAGE_LOCATION = s.STORAGE_LOCATION 
        WHERE 
            s.COMPANY_NO = @COMPANY
            AND TRANSACTION_DATE BETWEEN GETDATE() AND DATEADD(day, 31, GETDATE())
        GROUP BY 
            ITEM_NO, s.STORAGE_LOCATION
    )
    SELECT  
        m.MATERIAL_NO, 
        ISNULL(SUM(d.DEMAND_QTY), 1) AS PST_DEMAND_QTY, 
        ISNULL(SUM(r.RECEIPT_QTY), 1) AS PST_RECEIPT_QTY, 
        ISNULL(SUM(r.RECEIPT_QTY), 1) / ISNULL(SUM(d.DEMAND_QTY), 1) AS RATIO,
        SUM(m.INVENTORY_QTY) AS INVENTORY_QTY, 
        SUM(m.INVENTORY_VAL) AS INVENTORY_VAL, 
        ISNULL(SUM(f.DEMAND_QTY), 0) AS FUT_DEMAND_QTY, 
        ISNULL(SUM(f.DEMAND_VAL), 0) AS FUT_DEMAND_VAL,
        ISNULL(SUM(m.INVENTORY_QTY) - SUM(f.DEMAND_QTY), 0) AS  RMN_INVENTORY_QTY, 
        ISNULL(SUM(m.INVENTORY_VAL) - SUM(f.DEMAND_VAL), 0) AS RMN_INVENTORY_VAL
    FROM 
        #MATERIAL m 
    LEFT JOIN 
        #DEMAND d ON m.MATERIAL_NO = d.MATERIAL_NO 
                  AND m.STORAGE_LOCATION = d.STORAGE_LOCATION
    LEFT JOIN 
        #RECEIPT r ON m.MATERIAL_NO = r.MATERIAL_NO 
                   AND m.STORAGE_LOCATION = r.STORAGE_LOCATION
    LEFT JOIN 
        #DEMAND_FUT f ON m.MATERIAL_NO = f.MATERIAL_NO 
                      AND m.STORAGE_LOCATION = f.STORAGE_LOCATION
    GROUP BY 
        m.MATERIAL_NO 
    ORDER BY 
        m.MATERIAL_NO -- ISNULL(SUM(r.RECEIPT_QTY), 1) / ISNULL(SUM(d.DEMAND_QTY), 1) DESC
END

The result I get from the SQL statement:

The result I get from the DataTable:

I changed my code in C# back from async as I thought this would case the issue.

Answer 1

You are calling a stored procedure named sp_SIA_Get_Ratio_Demand_Receipt The WHERE clause is causing the discrepancy. To see what is being sent to SQL server you can use a profiler like SQL Server Profiler.