Standards

Simple - but true - it is more important to set the standards before you begin the project than anything else you can do.

Don't use sp_ as your prefix for stored procedures - treat these as reserved.
Consider using your company name or initials to generate site wide procedures e.g. scl_Update_Statistics
Mixed case procedures and views with capital table names helps to identify these easier
Document the common procedures - at the minimum with comments but preferably in a generic document for all to see.
If you have to read the TSQL twice to understand what it does - simplify it, the more legible the less likely it is to go wrong
Try to at the minimum get the main table in the procedure name.
Consider using scl_I_TableName for procedures which insert records and scl_U_TableName for update scl_D_TableName for delete procedures. Procedures with no I/U/D are defaulted to retrieval procedures.
Identify triggers easily by appending Trig to the name
Don't forget that there may be more than one row in a trigger - check the number of records in the deleted or inserted tables first
Never get details into a local variable and then do a select on the main tables - you'll only have the value of the first select