Process re-entrant

Published on: 28th Aug 2012

Updated on: 18th May 2021

Overview

For a data saving stored procedure, you may expect that the stored procedure will be calling it multiple times which is fine. But, it might not be true especially for scheduled stored procedures such as generating montly statement where the statement data in the database must be same the the printed copy (i.e., not editable after it has been printed). Another example is calculating the charges on the overdue invoice.

Unexpected re-entrant

What if the stored procedure that calculates the overdue charges runs more than one time on the same day? What will happen?

Why re-entrant

During the developing or debugging, you want to run the stored procedure as many time as possible so that you can fine tune the stored procedure or debug the process. But, once these stored procedures go live, it will run once for its' predefined interval.

How to prevent re-entrant in the first place

In order to prevent the stored procedure to run more than once for a day, you need to check if the process has been run on the predefined date or not.

For example, you have a table called "tb_whatever_process_log" which keeps track the processed date. Basically, in your stored procedure, the first thing to do is to make sure that it the stored procedure has never been run on that day before doing anything.

...
if exists(
    select *
    from tb_whatever_process_log
    where
        process_date = cast(convert(nvarchar, getdate(), 102) as datetime)
        and module = 'monthly-statement'
)
begin
    print 'Already done. Exit'
    set nocount off
    return
end
(... continue with the actual process..)

How to test the stored procedure that does not allowed re-entrant

Due to the re-entrant prevention condition, you will find it almost like no way to test out the stored procedure during the development phase. An easiest way to do this is to add a new parameter to the stored procedure call @is_debug int and it has a default value of 0. In the development phase, you will have to pass in the @is_debug = 1 in order to re-run the stored procedure.

Then, the modified stored procedure will look like this:

...
if @is_debug = 0
and exists(
    select *
    from tb_whatever_process_log
    where
        process_date = cast(convert(nvarchar, getdate(), 102) as datetime)
        and module = 'monthly-statement'
)
begin
    print 'Already done. Exit'
    set nocount off
    return
end
(... continue with the actual process..)

Jump to #MSSQL blog

Author

Lau Hon Wan, software developer.