Store procedure optimization -- best practices

1. Use stored procedures instead of heavy-duty queries.

2. Include the SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a Transact-SQL statement.
3. Call stored procedure using its fully qualified name.
4. Consider returning the integer value as an RETURN statement instead of an integer value as part of a recordset.
5. Don't use the prefix "sp_" in the stored procedure name if you need to create a stored procedure to run in a database other than the master database.
6. Use the sp_executesql stored procedure instead of the EXECUTE statement.
7. Use sp_executesql stored procedure instead of temporary stored procedures.
8. If you have a very large stored procedure, try to break down this stored procedure into several sub-procedures, and call them from a controlling stored procedure.
9. Try to avoid using temporary tables inside your stored procedure.
10. Try to avoid using DDL (Data Definition Language) statements inside your stored procedure.
11. Add the WITH RECOMPILE option to the CREATE PROCEDURE statement if you know that your query will vary each time it is run from the stored procedure.
12. Use SQL Server Profiler to determine which stored procedures has been recompiled too often.
 
 
For more detail please visit.http://www.mssqlcity.com/Tips/stored_procedures_optimization.htm