The 3 Primary Categories of SQL Stored Procedures
SQL stored procedures are used on many projects because of their capability and also because of their efficiency in the back-end. There are a lot of best practices that you pick up for writing your very own stored procedures and they typically fall into one of three categories.
For readability, use proper naming conventions. For example, if the procedure is used to insert, you can append “_Insert” to the end of the stored procedure name. This helps keeps the database organized and also helps in remembering the name when you need to reference the stored procedure in another query.
Also make sure to use comments in the header explaining the purpose of the procedure, your name, date the procedure was made, change dates for the procedures, and the estimated run time for the procedure. Throughout the procedure use comments for any complicated or confusing logic used. This not only helps spacing for readability but also for maintainability.
Another best practice for readability is to properly indent the code so it is easy to maintain and fix. This also helps you reference the code by line if each line is indented and has a specific purpose.
To improve performance, try limiting variables and use temp tables. This will improve efficiency and put less stress on the SQL engine itself.
Using the schema name when referencing tables is useful so the SQL engine doesn’t have to go through each schema when looking for a table.
Two other performance boosters are to set "no count" to "on" so, when the procedure runs, the number of rows affected doesn’t show. And second, please never use “select *” as it hinders performance. Select only the columns you need.
3) Handling Bugs
The best way to handle bugs is to take a good look at the code to see potential pain points and use try catch code at those areas to limit any potential breakage.
For more on the BI team at Exsilio and their work visit: https://blog.exsilio.com/business-intelligence