Stores procedure development guide

Below is some of our guideline in developing stored procedures:

The stored procedure must be test-able & traceable - the PRINT keyword must be use and/or sufficient log records must be created for system administrator to trace the process.

The stored procedure must be able to re-run - we should not expect the stored procedure will be run once. It should be tested with the idea what-if the caller run it for multiple times. What will happen? Another way of thinking is that, if we run the same stored procedure multiple times with the same parameters, does it give the same output?

The stored procedure must be broke down into small piece of process and meet the above guideline - sometimes, it's unbelievable to see a stored procedure which contains 1000+ lines. This is not a good way in developing stored procedure. The best way is to modularize the processes and break it down into small processes. This will ease the testing and also future enhancement.

Stop hard-coding the constants in a stored procedure - some programmer wants to complete their job as soon as possible without really care the future maintenance. The best way to handle constant is to save the value in a table and load it when you need it.

Stop using "flag" parameter to instruct the stored procedure to run different process - you should not use any parameter as a flag to indicate running which process within the stored procedure. For example, flag=1 is to returns customer records and flag=2 is to update customer's outstanding balance. This is very confusing.

Naming convention - don't name the stored procedure the way you like. All stored procedure must follow the agreed naming convention. In fact, the stored procedure name should contains the main module code (as prefix) to ease the future maintenance.