Stored procedures give you the following benefits
- Code reusability
- Access Control: You can control permission
- Execution plan reusability : and execution plan is generated for the stored procedure and reused the next time its executed. Compared to a standard SQL statement a execution plan is generated each time the code is executed.
- Prevent SQL Injection
- Procedure gives more Readability and Manageability.
Use SET NOCOUNT ON : Reduce the data transferred on the network. Database Engine, return the number of rows effected by the statements to the client which is unnecessary and you can avoid that using this statement. It is a must in all Stored procedure.
Formatting the stored procedure and queries : You must have a format / template for each object (sp/function/views) and everyone (the dev team) should stick to the format defined. And also the query has to be formatted well so that it is more readable.