Tips for Writing a Good Store Procedure Part I

Dynamic Queries :

• Do not use dynamic queries as much as possible because it compile the query everytime.
• If you use the simple sql query then it compiles only once bt dynamic query recompiles everytime.

Procedure Name Fully Qualified :

• Use the fully qualified name when you are calling stored procedures. This would be the format database_name.schema_name.table_name. For example, use EXEC master.dbo.Your_Proc_name instead of EXEC Your_Proc_name. 

• Also try to use the schema name when you create a procedure. Like: CREATE PROCEDURE dbo.Your_Proc_name instead of CREATE PROCEDURE Your_Proc_name 

sp_ Prefix :

• Dont use the "sp_" prefix in store procedure or anywhere cause it is a predefined format reseverd for system procedures.
• If by mistak name of store procedure will be same then it finds this procedure in system database and also in your database.So your database procedure never will be executed.

sp_executeSQL :

• It avoids recompilation of store procedure. 
• If you use dynamic query then it is very useful to you cause it stores procedures with the variable in cache.
• When you pass parameter you will get result without recompilation of store procedure.

KEEPFIXED PLAN options :

• It also avoids recompilation of store procedure.
• Use  this keyword "KEEPFIXED PLAN" for getting result from temporary tables.
• If your query contains this hint then your store procedure will not be recompiled.

SET NOCOUNT OFF :

• This returns the message that shows number of rows affected by SQL statement. This can cause extra network traffic and can have some serious impact on performance when the procedure is called frequently. 

SELECT vs SET :

• A single SELECT statement can assign values to different variables and is much faster than multiple SET statements assigning values to multiple different variables.
  
  SELECT @Var1 = @Var1 + 1, @Var2 = @Var2 - 1
  instead of
  
  
  SET @Var1 = @Var1 + 1

  SET @Var2 = @Var2 - 1