SQL server Stored Procedure temp variable value mismatching sometime

Question

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.

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

score 2 · Answer 1 · answered Apr 14 '17 at 13:32

I think @SqlWorldWide is onto the root cause of your issue, which is that pessimistic concurrency is allowing values to change between the time you make calls to the nested UDFs and when you insert values into Table1. If you either enable READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION on the database (both are unneeded as they accomplish relatively the same objective here) and run the statement again, I suspect you'll no longer run into data consistency issues.

Note: If you opt to enable ALLOW_SNAPSHOT_ISOLATION, please update your SP to include the SET TRANSACTION ISOLATION LEVEL SNAPSHOT command, as enabling this database setting also requires the transaction isolation level be explicitly defined in contrast to the READ_COMMITTED_SNAPSHOT setting, which implicitly enables this functionality across all queries within the database (ref).

Don't interpret this as both features function in the same way, rather they both achieve optimistic locking which likely will help with your situation. I would suggest enabling READ_COMMITTED_SNAPSHOT over ALLOW_SNAPSHOT_ISOLATION because I'm lazy and I've seen this setting benefit transactions more often than not at the database level. However, if you want to be surgical in your approach with optimistic concurrency, utilize the ALLOW_SNAPSHOT_ISOLATION feature instead which allows you to pick and choose which queries utilize optimistic concurrency via the SET TRANSACTION ISOLATION LEVEL operation.

SQL server Stored Procedure temp variable value mismatching sometime

1 Answers1