Stored Procedure Optimzation tips

1).Use NOCOUNT:

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.

2).Use return values:

Consider returning the integer value as an RETURN statement instead of an integer value as part of a recordset

3).Try to avoid using temporary tables inside your stored procedure.

4).Use SQL Server Profiler to determine which stored procedures has been recompiled too often.

5).Use sp_executesql stored procedure instead of temporary stored procedures.