Allow to run the process again?

For the stored procedure that is executed by the SQL Agent (i.e., the scheduler), you might have to prevent the same stored procedure run more than once.

Allow re-entrant

Not all processes allow to run more than once. For example, the stored procedure that is responsible for generating monthly statement. Another example is the stored procedure that is generating the exception report.

Why re-entrant
  
In the development phase, you might want to run the stored procedure as many time as possible so that you can fine tune and debug the process. But, once these stored procedures go live, it will run once for the day, week, month or whatever predefined date.

Now, how to prevent re-entrant
In order to prevent the stored procedure to run more than once for a day, you need to have a condition to check the log table. In our example, the "tb_whatever_process_log" table is keeping track the process date.

    if exists(
        select *
        from tb_whatever_process_log
        where
            process_date = cast(convert(nvarchar, getdate(), 102) as datetime)
    )
    begin
        print 'Already done. Exit'

        set nocount off
        return
    end

    (... continue with the actual process..)

How to test this stored procedure?

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 "@debug_mode int = 0". In the development phase, you will have to specify the "@debug_mode = 1".

How many lines should be written in a stored procedure?

I have an experience while participating in a project. While reviewing the stored procedure done by the programmer, I was having problem in understanding the stored procedure not because of it is complexity but it's length. One stored procedure consists of a few thousand lines of code. I was scratching my head and asked myself is it necessary to write everything in one stored procedure? No. It should be break into many stored procedures and then call by the main stored procedure.

Our stored procedure development guideline is quite simple and straight forward where it must:

• Must be able to re-test and/or re-use.
• Must be written in a maintainable way - if the stored procedure is handling small process, then, it should be easier to maintain.
• The stored procedure should contains less than 1,000 lines. I mean, that should be the maximum it should be.

How should we name the stored procedure?

As the project goes bigger and bigger, many enhancements were done to the existing systems, you might find out that the programmer must have discipline in naming the stored procedure and other database objects. Otherwise, the system maintenance is going to be a painful job.

Let me explain a bit on the stored procedure naming convention:

  "pr_" + "module initial" + "sub module initial" + "process name"

• The stored procedure name starts with "pr_" instead of "sp_" where "sp_" is meant for MSSQL system stored procedure. Basically, "pr_" stands for "procedure".
• "module initial" is the second section in the stored procedure name where it is short code or initial of the module name.
• "sub module" is the third section in the stored procedure name and it denotes the short code or initial for the sub-process.
• Lastly, "process name" is the actual process to be implemented within this stored procedure.

For example,

• pr_pos_rpt_sales - the module name is "pos" (i.e., Point Of Sales) and sub module name is "rpt" (i.e., Report). The "sales" means that it is sales data related process. From the stored procedure name, it is intuitive and it simply means that this stored procedure is handling the POS sales report generation.
• pr_ic_process_trans - the module name is "ic" (i.e., Inventory Control), the sub module name is "process" (i.e., process related) and "trans" is the short name for "transaction". This stored procedure is responsible for processing the inventory transactions.

For other database objects,

• "tb_" is for the table object.
• "fn_" is the function.
• "vw_" is the view.

How to design the reporting process that involves in multiple sub-processes

One problem that you might be facing (if you are doing the above mentioned) is that the main stored procedure is calling multiple sub-processes (i.e., other stored procedures) and generate/process some data and returns it to the main process. How do you simplify the entire process?

You need the following tables in order to split the entire process into smaller task which eases you from the testing to the maintenance:

• A table for storing the user parameter values - in this  table, it stores the user parameter values in a row. Each row is meant for one process session. By having this table, you will have to pass the process session ID to the sub-process and the sub-process will be able to load the parameter value from that is required. Another advantage of having this table is that you will be able to tell the actual parameter value that has been passed in. In case, the auditor would like to view what and who has been queried, you may retain the record in this table.
• A few report working tables - all the data that has been processed will be stored in this table for other module to pickup. For example, the report writer will query this table for the final output. The reason why you need many working tables is that it allows you to query the necessary data and stores into first table. Then, run an aggregate process and store it into another table as the final output. This will be very useful when it's not straight forward for you to get the final output by using one SELECT statement.

The stored procedure design:

• A stored procedure that is responsible for saving the parameter into the parameter table.
• All stored procedures must have "sess_id" (i.e., the process session ID in UniqueIdentifier type).

Note: please take note that the above design is relying on "sess_id" (i.e., the process session ID in UniqueIdentifier type).

Real life example:

• Generating Balance Sheet report (i.e., an Accounting report) which consist of multiple sections that includes at least the fixed assets, current asset, current liabilities and capital. The raw data might be stored in different table and requires some aggregate over the historical data.