Here’re guideline to write DB2 Stored procedures. You can use these for other RDBMS also.
The purpose is easy maintenance. Also, improves readability.
- Avoid GO TO statements
- Use indentation while writing loops
- Use meaningful Lables
- If Triggers or Function is too large, you can convert it to a procedure.
- If SQL procedure is too small, you can convert it to a function
- Group all the procedures of the same application under one schema
- For instance – payroll.add_employee/payroll.remove_employee
- Use proper names while CREATE, DELETE and UPDATE of stored procedures
- Always us SQLSTATE to handle it
- Don’t use SQLCODE
COMMIT and ROLLBACK
- Use COMMIT and ROLLBACK explicitly