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

• Scott Hodgin - Retired: My first thought is not about the temp table, but the query that is running to populate the temp table - are you 'sure' that query is not the actual problem? Posting the code for the stored procedure might help us.
• SqlWorldWide: Is this a temporary table or table variable where you are holding your intermediate result set and giving you inconsistent result set?
• SqlWorldWide: Please see this question stackoverflow.com/questions/22639164/…
• Tufan Chand: it's temporary variable @SqlWorldWide
• SqlWorldWide: Some of your final result set(variable values for inserting) for example @POC_Completed_Till_Date depends on 2 function calls. What will happen if underlying data changes between calls. Are you expecting different result?

Answer 1

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.