Do not use prefix in your stored procedures, i.e., do not prefix them with sp_. Microsoft ships system procedures which are prefixed with sp_. So if you prefix your procedures with sp_, SQL Server will first search in the master database for the procedure and then in your application database. Again, this is a bottleneck.

Always use exception handling if you are working with transaction based procedures. Proper error handling ensures security and provides a better approach of what to-do when an unexpected error occurs.

If you do not want your client application to check the rows affected for an operation, then it is advisable to use SET NOCOUNT ON in your stored procedures. Not using this would send the number of rows affected to the client application or ADO/ADO.NET. The client application would further work on this result through the command or connection objects. This could cause extra overhead on both the client and server.

Efficient Execution Considerations

When trying to execute your T-SQL code, do not perform index or table scans. You can have index seek instead of index scan. Also, try to evaluate hash joins, filters, sort conditions and bookmarks. A better decision could be made on the execution strategy by observing the execution plan. When working with dynamic SQL, things may not work as anticipated during execution. Although it is not a great idea having your queries dynamically built, in some cases they help reduce the code when the SQL expression needs to be built upon many decisions. In such cases you can always use sp_executesql. Also, while working with stored procedures it is advised not to mix up the DML and DDL statements.