Sql-server – SQL server Stored Procedure temp variable value mismatching sometime

sql servertempdb

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

Best Answer

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.

Related Solutions

SSIS – How to Use Optional Variables/Parameters

You may be interested in a standard approach to solve this problem. It is something called short circuit. To illustrate it, here's the code:

DECLARE @chooseDate datetime2
--SET @chooseDate = '1966-04-08'
SET @chooseDate = NULL
SELECT *
FROM DimCustomer
WHERE @chooseDate IS NULL OR BirthDate = @chooseDate

This way you can explicitly set variable to NULL, do not set variable to anything, or specify a value.

The reason it is called short circuit is that based on the implementation of ANSI SQL this line of code:

WHERE @chooseDate IS NULL OR BirthDate = @chooseDate

has a possibility to evaluate only the first part of the expression (the IS NULL part) thus excluding the need to evaluate the second part, which makes the code slightly faster.

SQL Server – Create Function to Generate Column Value in Specific Format

One way would be to add an ID column in your users table, with a default value based on a sequence object, then add a computed column to create the key using a date conversion to string.

To create the sequence, something like:

CREATE SEQUENCE [dbo].[userid_sq] 
 AS [int]
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 500
 CYCLE

I wouldn't limit yourself to a value of 99 if you are not 100% sure you are going to stay under that number of users per day.

You want a fresh cycle every day, right? So you can force it with an evening job/script to ensure it is back at 1 the next day.

ALTER SEQUENCE [dbo].[userid_sq]  RESTART WITH 1;

So just to illustrate the point, say I'm creating a new table, UserDetails, and want to include this logic, I might do something like:

CREATE TABLE UserDetails (
    ID INT NOT NULL DEFAULT NEXT VALUE FOR userid_sq
    ,Created DATETIME DEFAULT GETDATE()
    ,UserKey AS 
        'K' + 
        CONVERT(VARCHAR(6), Created, 12) + 
        RIGHT(CONVERT(VARCHAR(11), 1000 + ID), 3)
    ,UserName VARCHAR(50) NOT NULL
    )

INSERT INTO UserDetails (UserName)
VALUES ('TestUser')

My table content would be:

ID       - 1
UserKey  - K160531001
UserName - TestUser

You asked about indexing, so, ignoring the fact that the above example would be non-deterministic and therefore ineligible to be used a primary key, if you want to index the computed column, you'll need to PERSIST the column in the database. This stores the values to disk rather than as a calculated virtual value.

ALTER TABLE table_name ALTER COLUMN computed_column_name ADD PERSISTED;

Note that there are various restrictions with what you can do with computed columns so I would implore you to read the following information to get a proper understanding.

MSDN Link

Here's a relevant snippet from that article, which also relates to the above example:

A computed column cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition. However, if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns, a computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint. For example, if the table has integer columns a and b, the computed column a + b may be indexed, but computed column a + DATEPART(dd, GETDATE()) cannot be indexed, because the value might change in subsequent invocations.

A computed column cannot be the target of an INSERT or UPDATE statement.

Best Answer

Related Solutions

SSIS – How to Use Optional Variables/Parameters

SQL Server – Create Function to Generate Column Value in Specific Format

Related Question