Store Procedure Optimization Tips:-

1. Include 'SET NOCOUNT ON' Statement.
2. Use Schema name with object names.
         [dbo.MyTable ]
3. Don't  Use the Prefix 'SP_' in the Store Procedure name.
4. Use IF Exists (SELECT 1) Instead of (SELECT *).
5. Use the 'SP_ExecuteSQL' Store procedure instead of Execute Starement.
6. Avoid using SQL Server Cursor whenever Possible.
7. Keep the transaction as short as possible.
8. Use TRY_CATCH for error handling.