In my day-to-day work, I have to manage a lot of different "fancy" way to write stored procedures. Every single T-SQL code Dev in every single Company has his/her personal way to write stored procedures. This is not a good approach because

  • it should happen someone else has to make changes to sp code;
  • it takes time to understand how the code is written and managed;
  • if many people makes changes to sp code, using his own style, the result is a puzzle and not a Stored Procedures

So I decided to write a template for Stored Procedures where:

  • Stored Procedure name does not have sp_ prefix;
  • Stored Procedure name has the right schema name;
  • parameters have prefix: @p_ (i.e. @p_ParameterName) to distinguish them from variables;
  • local variables have no prefix: @VariableName;
  • Parameters and variables must have the right datatype to avoid CAST and CONVERT inside the Stored Procedure or, even worse, implicit conversion;
  • SET the right OPTIONS;
  • TRY.... CATCH in the right position;