CODEWITHSUNDEEP
c#sql-serverstored-procedures
Cody Cook
Cody Cook

Reputation: 111

Stored Procedure Skipping over Update Statement

I am using an EXE to call a stored procedure a couple of times an hour to move data from one set of tables to another, while also updating a count in another table to show how many times the stored procedure ran. The EXE calls the stored procedure with a certain number to tell the stored procedure which insert to run. So every hour, this procedure is ran twice:

If @intUploadNumber = 1
INSERT INTO Prod1
SELECT *
FROM Staging1

UPDATE tbl_Count
SET UpdateCount = UpdateCount + 1
WHERE Date = CAST(GETDATE() AS DATE)

If @intUploadNumber = 2
INSERT INTO Prod2
SELET *
FROM Staging2

UPDATE tbl_Count
SET UpdateCount = UpdateCount + 1
WHERE Date = CAST(GETDATE() AS DATE)

The EXE basically calling the procedure by:

exec STRPRC 1
exec STRPRC 2

At the bottom of the stored procedure is the following:

IF ((select updatecount from tbl_Count WHERE Date = CAST(GETDATE() AS DATE)) % 2 = 0)
BEGIN 
INSERT INTO Prod3
SELECT *
FROM PROD1
LEFT JOIN PROD2 ON PROD1.CID = PROD2.CID
END

However, I am noticing with my code, that even though both exec(s) are called properly, and everything from staging went into production, the stored procedure will often times pass over the last if statement, not executing my final insert.

Does anyone know of a issue with MS SQL that would allow this to happen? Maybe because both times the stored procedure is called the updatecount isn't being set properly by the time it hits the final if statement? I also have a TRY...CATCH surrounding the SQL that should dump all of my errors into another table, but it remains empty. If more detail is required, please let me know. (No, I cannot move away from the EXE calling both of the initial inserts separately, they must be called separately.)

Thank you

Upvotes: 1

Views: 646

Answers (1)

Steve
Steve

Reputation: 216303

Suppose that you call this Stored Procedure with the parameter 1 and at that point in time your UpdateCount is an odd value (let's assume 1)

Now the flow of your code in the SP is the following:

Is @intUploadNumber = 1       => Yes then Insert
set updatecount to itself + 1 => updatecount is 2
Is @intUploadNumber = 2       => No then skip the Insert 
set updatecount to itself + 1 => updatecount is 3

At this point the % (MOD) operation in the last IF statement gives you 1 instead of 0 and thus the final INSERT is not executed

I presume that you want to increment the UpdateCount just one time when the SP is called not two times. So you can simply remove the first UPDATE statement and leave only the last one.
However I prefer to always use the BEGIN/END block delimiters even when you have only one statement to execute

IF @intUploadNumber = 1
    BEGIN
        INSERT INTO Prod1 SELECT * FROM Staging1
    END
ELSE 
    BEGIN
        IF @intUploadNumber = 2
           BEGIN
              INSERT INTO Prod2 SELECT * FROM Staging2
           END
        ELSE
           RAISERROR ('Invalid parameter value',16,1)  
    END

UPDATE tbl_Count SET UpdateCount = UpdateCount + 1
WHERE Date = CAST(GETDATE() AS DATE)

Upvotes: 2

Related Questions