Bumped by Community user
    Bumped by Community user
4 corrected spelling , fixed grammar, fixed technical terms.
source | link

EDIT : Exact SP

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[PROC_Insert_Testing] (@Contract_Id varchar(24), 
@Year int, @Month int, @EmpID varchar(10))
AS
BEGIN  

DECLARE @Onsite_Actual_Cost numeric(24,10),  
        @Offshore_Actual_Cost numeric(24,10),  
        @Misc_Total_Actual_Cost numeric(24,10),  
        @Misc_Total_Remaining_Cost numeric(24,10),  
        @Numerator numeric(24,10),  
        @Onsite_Cumulative_Cost numeric(24,10),  
        @Offshore_Cumulative_Cost numeric(24,10),  
        @Misc_Cumulative_Cost numeric(24,10),  
        @Denominator numeric(24,10),  
        @POC_Completed_Till_Date numeric(24,10),  
        @version numeric(9,2),  
        @Contract_Value numeric(24,10),  
        @POC_Revenue_Till_Date numeric(24,10),  
        @POC_Percentage_Till_Last_Month numeric(24,10),  
        @POC_Value_Till_Last_Month numeric(24,10),  
        @Previous_Year int,  
        @Previous_Month int,  
        @OffRate numeric(24,10),  
        @OnRate numeric(24,10),
        @POC_Value_LastMonth_In_USD NUMERIC(24,10),
        @POC_Value_CurrentMonth_In_USD NUMERIC(24,10),
        @USD_Conversion_Factor NUMERIC(24,10),
        @POC_Value_Till_Last_Month_In_USD NUMERIC(24,10),
        @Company_Code VARCHAR(10), 
        @Currency_Code VARCHAR(6), 
        @SOW VARCHAR(10)

BEGIN   TRANSACTION  

IF EXISTS (SELECT 1 FROM  Table1 WHERE Contract_Id = @Contract_Id AND EAB_Month = @Month AND EAB_Year = @Year)  
BEGIN  
    Delete  FROM Table1 
    WHERE   Contract_Id = @Contract_Id 
    AND     EAB_Month = @Month 
    AND     EAB_Year = @Year  

    IF @@ERROR <> 0  
    BEGIN  
        ROLLBACK  
        RAISERROR ('Error IN deleting data into Table1',16,1)  
        RETURN  
    END  
END  

SELECT  @Company_Code = SUBSTRING(B.Company_code,1,2),
        @Currency_Code = A.CurrencyCode
FROM    Table2 A
INNER   JOIN
        Table3 B
ON      A.SOW = B.SOW
WHERE   A.Contract_ID = @Contract_ID


SELECT  @OffRate = dbo.Fn_Get_Offshore_Rate(@Contract_Id),   
       @OnRate = dbo.Fn_Get_Onsite_Rate(@Contract_Id)  

    ----------------------------- To Calculate the numerator part :(Onsite Total Effort *Onsite rate)+(Offshore Total Effort *Offshore Rate) -----------------------------
    SELECT  @Onsite_Actual_Cost = Actual_Effort_Total * @OnRate
    FROM    Table4
    WHERE   Contract_Id = @Contract_Id
    AND     EAB_Year=@year
    AND     EAB_Month=@Month
    AND     Estimate_Effort_Type = 'On'

    SELECT  @Offshore_Actual_Cost = Actual_Effort_Total * @OffRate
    FROM    Table4
    WHERE   Contract_Id = @Contract_Id
    AND     EAB_Year = @year
    AND     EAB_Month = @Month
    AND     Estimate_Effort_Type = 'Off'

    ----------------------------- Added by Natarajan ON June 2005 to impplement Wt.POC Formula -----------------------------        
    SELECT  @Misc_Total_Actual_Cost = ISNULL(SUM(a.Actual_Effort_Total),0)  
    FROM    Table4 a  
    LEFT    OUTER JOIN  
            poc_cost_type_master b  
    ON      LTRIM(RTRIM(a.miscost_type)) = LTRIM(RTRIM(b.cost_type_id))
    WHERE   a.Contract_Id = @Contract_Id
    AND     a.EAB_Year = @year
    AND     a.EAB_Month = @Month
    AND     a.Estimate_Effort_Type NOT IN ('Off','On','Forex')
    AND     ISNULL(b.cost_use_in_formula, 1) = 1 -- inculde only cost used to calculat POC (eg. service cost)

    SET     @Numerator = @Onsite_Actual_Cost + @Offshore_Actual_Cost + @Misc_Total_Actual_Cost

    ----------------------------- To Calculate the Denominator  part :(Onsite Total Effort + BTG)  *Onsite rate added to ( Offshore Total Effort + Offshore BTG ) *Offshore Rate -----------------------------

    SELECT  @Onsite_Cumulative_Cost = (Actual_Effort_Total + BTG + BTG_Contigency + ((ISNULL(BTG_Additional_Contigency,0) * ISNULL(BTG,0)) / 100 )) * @OnRate
    FROM    Table4
    WHERE   Contract_Id = @Contract_Id
    AND     EAB_Year = @year
    AND     EAB_Month = @Month
    AND     Estimate_Effort_Type = 'On'


    SELECT  @Offshore_Cumulative_Cost = (Actual_Effort_Total + BTG + BTG_Contigency + ((ISNULL(BTG_Additional_Contigency,0) * ISNULL(BTG,0)) / 100 )) * @OffRate
    FROM    Table4
    WHERE   Contract_Id = @Contract_Id
    AND     EAB_Year = @year
    AND     EAB_Month = @Month
    AND     Estimate_Effort_Type = 'Off'

    ----------------------------- Added by Natarajan ON June 2005 to impplement Wt.POC Formula -----------------------------        
    SELECT  @Misc_Total_Remaining_Cost = ISNULL(sum(a.BTG),0)
    FROM    Table4 a
    LEFT    OUTER JOIN  
            poc_cost_type_master b  
    ON      LTRIM(RTRIM(a.miscost_type)) = LTRIM(RTRIM(b.cost_type_id))  
    WHERE   a.Contract_Id = @Contract_Id 
    AND     a.EAB_Year = @year 
    AND     a.EAB_Month = @Month  
    AND     a.Estimate_Effort_Type NOT IN ('Off','On','Forex')  
    AND     ISNULL(b.cost_use_in_formula, 1) = 1 -- inculde only cost used to calculat POC (eg. service cost)  

    SELECT  @Misc_Cumulative_Cost = ISNULL((@Misc_Total_Actual_Cost + @Misc_Total_Remaining_Cost),0)  
    SET     @Denominator = @Onsite_Cumulative_Cost + @Offshore_Cumulative_Cost + @Misc_Cumulative_Cost

---------------------------- To calculate POC -----------------------------
IF  (0 <> @Denominator)  
    SET @POC_Completed_Till_Date = (@Numerator / @Denominator) * 100  
ELSE  
    SET @POC_Completed_Till_Date = 0

----------------------------- To calculate Revenue :POC * Contract  Value ----------------------------- 
SELECT  @version = Max(Version) 
FROM    Table5  
WHERE   Contract_Id = @Contract_Id 
AND     Flag = 'F'  

SELECT  @Contract_Value =SUM(MiscValue) 
FROM    Table6  
WHERE   Contract_Id = @Contract_Id 
AND     Version=@Version      

SET     @POC_Revenue_Till_Date = @POC_Completed_Till_Date * @Contract_Value / 100  

----------------------------- To get  POC - AND Revenue for the previous month ----------------------------- 
SELECT  @POC_Percentage_Till_Last_Month = ISNULL(dbo.POC_fn_get_POC_TP(@Contract_Id,@Month,@Year),0),  
        @POC_Value_Till_Last_Month = ISNULL(dbo.POC_fn_RR_TillLastMonth(@Contract_Id,@Month,@Year),0),
        @POC_Value_Till_Last_Month_In_USD = ISNULL(dbo.POC_fn_RR_TillLastMonth_In_USD(@Contract_Id,@Month,@Year),0)

SET     @USD_Conversion_Factor = ISNULL(DBO.POC_Fn_Get_Revenue_In_USD(@Contract_ID),1)      

SET @POC_Value_CurrentMonth_In_USD = (((ISNULL(@POC_Revenue_Till_Date,0) - ISNULL(@POC_Value_Till_Last_Month,0)) *
        ISNULL(@USD_Conversion_Factor,0)) + ISNULL(@POC_Value_Till_Last_Month_In_USD,0))

----------------------------- Inserting to POC table ----------------------------- 

INSERT  INTO Table1 VALUES(@Contract_Id,@Year,@Month,  
@POC_Percentage_Till_Last_Month,@POC_Value_Till_Last_Month,  
@POC_Completed_Till_Date,@POC_Revenue_Till_Date,'Y',@EmpID,GETDATE(),
ISNULL(@POC_Value_Till_Last_Month_In_USD,0),
ISNULL(@POC_Value_CurrentMonth_In_USD,0))

IF @@ERROR <> 0  
BEGIN  
    ROLLBACK  
    RAISERROR ('Error IN inserting data into Table1',16,1)  
    RETURN  
END  
COMMIT  
END

EDIT : Exact SP

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[PROC_Insert_Testing] (@Contract_Id varchar(24), 
@Year int, @Month int, @EmpID varchar(10))
AS
BEGIN  

DECLARE @Onsite_Actual_Cost numeric(24,10),  
        @Offshore_Actual_Cost numeric(24,10),  
        @Misc_Total_Actual_Cost numeric(24,10),  
        @Misc_Total_Remaining_Cost numeric(24,10),  
        @Numerator numeric(24,10),  
        @Onsite_Cumulative_Cost numeric(24,10),  
        @Offshore_Cumulative_Cost numeric(24,10),  
        @Misc_Cumulative_Cost numeric(24,10),  
        @Denominator numeric(24,10),  
        @POC_Completed_Till_Date numeric(24,10),  
        @version numeric(9,2),  
        @Contract_Value numeric(24,10),  
        @POC_Revenue_Till_Date numeric(24,10),  
        @POC_Percentage_Till_Last_Month numeric(24,10),  
        @POC_Value_Till_Last_Month numeric(24,10),  
        @Previous_Year int,  
        @Previous_Month int,  
        @OffRate numeric(24,10),  
        @OnRate numeric(24,10),
        @POC_Value_LastMonth_In_USD NUMERIC(24,10),
        @POC_Value_CurrentMonth_In_USD NUMERIC(24,10),
        @USD_Conversion_Factor NUMERIC(24,10),
        @POC_Value_Till_Last_Month_In_USD NUMERIC(24,10),
        @Company_Code VARCHAR(10), 
        @Currency_Code VARCHAR(6), 
        @SOW VARCHAR(10)

BEGIN   TRANSACTION  

IF EXISTS (SELECT 1 FROM  Table1 WHERE Contract_Id = @Contract_Id AND EAB_Month = @Month AND EAB_Year = @Year)  
BEGIN  
    Delete  FROM Table1 
    WHERE   Contract_Id = @Contract_Id 
    AND     EAB_Month = @Month 
    AND     EAB_Year = @Year  

    IF @@ERROR <> 0  
    BEGIN  
        ROLLBACK  
        RAISERROR ('Error IN deleting data into Table1',16,1)  
        RETURN  
    END  
END  

SELECT  @Company_Code = SUBSTRING(B.Company_code,1,2),
        @Currency_Code = A.CurrencyCode
FROM    Table2 A
INNER   JOIN
        Table3 B
ON      A.SOW = B.SOW
WHERE   A.Contract_ID = @Contract_ID


SELECT  @OffRate = dbo.Fn_Get_Offshore_Rate(@Contract_Id),   
       @OnRate = dbo.Fn_Get_Onsite_Rate(@Contract_Id)  

    ----------------------------- To Calculate the numerator part :(Onsite Total Effort *Onsite rate)+(Offshore Total Effort *Offshore Rate) -----------------------------
    SELECT  @Onsite_Actual_Cost = Actual_Effort_Total * @OnRate
    FROM    Table4
    WHERE   Contract_Id = @Contract_Id
    AND     EAB_Year=@year
    AND     EAB_Month=@Month
    AND     Estimate_Effort_Type = 'On'

    SELECT  @Offshore_Actual_Cost = Actual_Effort_Total * @OffRate
    FROM    Table4
    WHERE   Contract_Id = @Contract_Id
    AND     EAB_Year = @year
    AND     EAB_Month = @Month
    AND     Estimate_Effort_Type = 'Off'

    ----------------------------- Added by Natarajan ON June 2005 to impplement Wt.POC Formula -----------------------------        
    SELECT  @Misc_Total_Actual_Cost = ISNULL(SUM(a.Actual_Effort_Total),0)  
    FROM    Table4 a  
    LEFT    OUTER JOIN  
            poc_cost_type_master b  
    ON      LTRIM(RTRIM(a.miscost_type)) = LTRIM(RTRIM(b.cost_type_id))
    WHERE   a.Contract_Id = @Contract_Id
    AND     a.EAB_Year = @year
    AND     a.EAB_Month = @Month
    AND     a.Estimate_Effort_Type NOT IN ('Off','On','Forex')
    AND     ISNULL(b.cost_use_in_formula, 1) = 1 -- inculde only cost used to calculat POC (eg. service cost)

    SET     @Numerator = @Onsite_Actual_Cost + @Offshore_Actual_Cost + @Misc_Total_Actual_Cost

    ----------------------------- To Calculate the Denominator  part :(Onsite Total Effort + BTG)  *Onsite rate added to ( Offshore Total Effort + Offshore BTG ) *Offshore Rate -----------------------------

    SELECT  @Onsite_Cumulative_Cost = (Actual_Effort_Total + BTG + BTG_Contigency + ((ISNULL(BTG_Additional_Contigency,0) * ISNULL(BTG,0)) / 100 )) * @OnRate
    FROM    Table4
    WHERE   Contract_Id = @Contract_Id
    AND     EAB_Year = @year
    AND     EAB_Month = @Month
    AND     Estimate_Effort_Type = 'On'


    SELECT  @Offshore_Cumulative_Cost = (Actual_Effort_Total + BTG + BTG_Contigency + ((ISNULL(BTG_Additional_Contigency,0) * ISNULL(BTG,0)) / 100 )) * @OffRate
    FROM    Table4
    WHERE   Contract_Id = @Contract_Id
    AND     EAB_Year = @year
    AND     EAB_Month = @Month
    AND     Estimate_Effort_Type = 'Off'

    ----------------------------- Added by Natarajan ON June 2005 to impplement Wt.POC Formula -----------------------------        
    SELECT  @Misc_Total_Remaining_Cost = ISNULL(sum(a.BTG),0)
    FROM    Table4 a
    LEFT    OUTER JOIN  
            poc_cost_type_master b  
    ON      LTRIM(RTRIM(a.miscost_type)) = LTRIM(RTRIM(b.cost_type_id))  
    WHERE   a.Contract_Id = @Contract_Id 
    AND     a.EAB_Year = @year 
    AND     a.EAB_Month = @Month  
    AND     a.Estimate_Effort_Type NOT IN ('Off','On','Forex')  
    AND     ISNULL(b.cost_use_in_formula, 1) = 1 -- inculde only cost used to calculat POC (eg. service cost)  

    SELECT  @Misc_Cumulative_Cost = ISNULL((@Misc_Total_Actual_Cost + @Misc_Total_Remaining_Cost),0)  
    SET     @Denominator = @Onsite_Cumulative_Cost + @Offshore_Cumulative_Cost + @Misc_Cumulative_Cost

---------------------------- To calculate POC -----------------------------
IF  (0 <> @Denominator)  
    SET @POC_Completed_Till_Date = (@Numerator / @Denominator) * 100  
ELSE  
    SET @POC_Completed_Till_Date = 0

----------------------------- To calculate Revenue :POC * Contract  Value ----------------------------- 
SELECT  @version = Max(Version) 
FROM    Table5  
WHERE   Contract_Id = @Contract_Id 
AND     Flag = 'F'  

SELECT  @Contract_Value =SUM(MiscValue) 
FROM    Table6  
WHERE   Contract_Id = @Contract_Id 
AND     Version=@Version      

SET     @POC_Revenue_Till_Date = @POC_Completed_Till_Date * @Contract_Value / 100  

----------------------------- To get  POC - AND Revenue for the previous month ----------------------------- 
SELECT  @POC_Percentage_Till_Last_Month = ISNULL(dbo.POC_fn_get_POC_TP(@Contract_Id,@Month,@Year),0),  
        @POC_Value_Till_Last_Month = ISNULL(dbo.POC_fn_RR_TillLastMonth(@Contract_Id,@Month,@Year),0),
        @POC_Value_Till_Last_Month_In_USD = ISNULL(dbo.POC_fn_RR_TillLastMonth_In_USD(@Contract_Id,@Month,@Year),0)

SET     @USD_Conversion_Factor = ISNULL(DBO.POC_Fn_Get_Revenue_In_USD(@Contract_ID),1)      

SET @POC_Value_CurrentMonth_In_USD = (((ISNULL(@POC_Revenue_Till_Date,0) - ISNULL(@POC_Value_Till_Last_Month,0)) *
        ISNULL(@USD_Conversion_Factor,0)) + ISNULL(@POC_Value_Till_Last_Month_In_USD,0))

----------------------------- Inserting to POC table ----------------------------- 

INSERT  INTO Table1 VALUES(@Contract_Id,@Year,@Month,  
@POC_Percentage_Till_Last_Month,@POC_Value_Till_Last_Month,  
@POC_Completed_Till_Date,@POC_Revenue_Till_Date,'Y',@EmpID,GETDATE(),
ISNULL(@POC_Value_Till_Last_Month_In_USD,0),
ISNULL(@POC_Value_CurrentMonth_In_USD,0))

IF @@ERROR <> 0  
BEGIN  
    ROLLBACK  
    RAISERROR ('Error IN inserting data into Table1',16,1)  
    RETURN  
END  
COMMIT  
END
3 corrected spelling , fixed grammar, fixed technical terms.
source | link

I have created one stored procedure (sp) in SQL server and that sp contains temporary tablesVariables for holding calculated values. Later in my code I am inserting those values to one of the permanent table. Sometimes ( about 5 out of 100 cases) wrong values from the temp tablevariable is getting inserted to my permanent table.

If I am executing the same sp manually with same parameters it is returning the correct values. This sp takes only one unique id as parameter value and use this id to fetch corresponding record, do some calculations and stores values into the temp tablevariable.

I know that variable values can be shared only for the same process or session, I am quite surprise to see that I am getting wrong values inserted into the temppermanent table.

  • Is it for any kind of load issues as during that time large no of users using that feature?
  • Is it for any kind of settings issue in SQL server?
  • Is there anything else that can cause this issue ?

I feel this is very rare and not possible as per the default functionality but i am getting this. Please let me know if anyone need some more information.

Can anyone help me to figure out this problem? Please provide any kind of suggestion and will be highly appreciated.

I have created one stored procedure (sp) in SQL server and that sp contains temporary tables for holding calculated values. Later in my code I am inserting those values to one of the permanent table. Sometimes ( about 5 out of 100 cases) wrong values from the temp table is getting inserted to my permanent table.

If I am executing the same sp manually with same parameters it is returning the correct values. This sp takes only one unique id as parameter value and use this id to fetch corresponding record, do some calculations and stores values into the temp table.

I know that variable values can be shared only for the same process or session, I am quite surprise to see that I am getting wrong values inserted into the temp table.

  • Is it for any kind of load issues as during that time large no of users using that feature?
  • Is it for any kind of settings issue in SQL server?
  • Is there anything else that can cause this issue ?

I feel this is very rare and not possible as per the default functionality but i am getting this. Please let me know if anyone need some more information.

Can anyone help me to figure out this problem? Please provide any kind of suggestion and will be highly appreciated.

I have created one stored procedure (sp) in SQL server and that sp contains temporary Variables for holding calculated values. Later in my code I am inserting those values to one of the permanent table. Sometimes ( about 5 out of 100 cases) wrong values from the temp variable is getting inserted to my permanent table.

If I am executing the same sp manually with same parameters it is returning the correct values. This sp takes only one unique id as parameter value and use this id to fetch corresponding record, do some calculations and stores values into the temp variable.

I know that variable values can be shared only for the same process or session, I am quite surprise to see that I am getting wrong values inserted into the permanent table.

  • Is it for any kind of load issues as during that time large no of users using that feature?
  • Is it for any kind of settings issue in SQL server?
  • Is there anything else that can cause this issue ?

I feel this is very rare and not possible as per the default functionality but i am getting this. Please let me know if anyone need some more information.

Can anyone help me to figure out this problem? Please provide any kind of suggestion and will be highly appreciated.

2 corrected spelling , fixed grammar, fixed technical terms.
source | link

I have created one stored procedure (sp) in sqlSQL server and that sp contains some temp variablestemporary tables for holding some calculated values and after that i. Later in my code I am inserting those values to one of mythe permanent table. But sometimes meansSometimes ( about 5 out of 100 cases some) wrong valuevalues from the temp table is getting inserted to my permanent table.  

After that whenIf I am executing manually thethe same sp manually with same parameters it is returning the correct values. The parameters to theThis sp istakes only one unique id as parameter value and byuse this unique id the respective records are getting fetched to fetch corresponding record, calculateddo some calculations and stores to thatvalues into the temp variable.

So how for some cases wrong value is getting calculated and when i am executing again getting some different valuetable.

I know that temp variable values can be shared only for the same process or session, iI am quite surprise to see that how is it possibleI am getting wrong values inserted into the temp table.

Is it for any kind of load issues as during that time large no of users using that feature?

Is it for any kind of settings issue in sql server?

Or is there anything that can cause this issue ?

  • Is it for any kind of load issues as during that time large no of users using that feature?
  • Is it for any kind of settings issue in SQL server?
  • Is there anything else that can cause this issue ?

I feel this is very rare kind of thing and not possible as per the default functionality but i am getting this. Please let me know if anyone need some more information.

Can anyone help me to figure out this problem? Please provide any kind of suggestion willand will be highly appreciated.

I have created one sp in sql server and that sp contains some temp variables for holding some calculated values and after that i am inserting those values to one of my table. But sometimes means 5 out of 100 cases some wrong value is getting inserted to my table.  

After that when I am executing manually the same sp with same parameters it is returning the correct values. The parameters to the sp is only one unique id and by this unique id the respective records are getting fetched , calculated and stores to that temp variable.

So how for some cases wrong value is getting calculated and when i am executing again getting some different value.

I know that temp variable values can be shared only for the same process or session, i am quite surprise that how is it possible.

Is it for any kind of load issues as during that time large no of users using that feature?

Is it for any kind of settings issue in sql server?

Or is there anything that can cause this issue ?

I feel this is very rare kind of thing and not possible as per the default functionality but i am getting this. Please let me know if anyone need some more information.

Can anyone help me to figure out this problem any kind of suggestion will be highly appreciated.

I have created one stored procedure (sp) in SQL server and that sp contains temporary tables for holding calculated values. Later in my code I am inserting those values to one of the permanent table. Sometimes ( about 5 out of 100 cases) wrong values from the temp table is getting inserted to my permanent table.

If I am executing the same sp manually with same parameters it is returning the correct values. This sp takes only one unique id as parameter value and use this id to fetch corresponding record, do some calculations and stores values into the temp table.

I know that variable values can be shared only for the same process or session, I am quite surprise to see that I am getting wrong values inserted into the temp table.

  • Is it for any kind of load issues as during that time large no of users using that feature?
  • Is it for any kind of settings issue in SQL server?
  • Is there anything else that can cause this issue ?

I feel this is very rare and not possible as per the default functionality but i am getting this. Please let me know if anyone need some more information.

Can anyone help me to figure out this problem? Please provide any kind of suggestion and will be highly appreciated.

1
source | link