Here’s a link to a great article I read recently that summarized in a concise package things not to do when using SQL Server as a data-persistence solution, which is almost more important than what to do.
Note: SQL cursors especially – please never use cursors of any flavor in your stored procedures. There is almost always a better set-based method to accomplish your end goal.
As it’s not mentioned in the following article, I will add that not making use of SQL Server metadata is a mistake I see very frequently. What is metadata? It’s data that describes other data or data structures. It’s particularly easy to implement with SQL Server once you know what to do and how to use it. There is probably no easier way to document a database for all users or stakeholders involved than to implement/update SQL Server metadata, and there are no good reasons not to utilize it. It’s easy and freely available – just waiting to be used. I have a set of stored procedures developed that make the process absolutely painless for keeping SQL Server database metadata updated at the table, column, view, and function/param levels (all the places that really count!), and across all environments (production, pilot, test).