There are many tips and tricks for the same. Here are few:
- Include SET NOCOUNT ON statement.
- Use schema name with object name.
- Do not use the prefix “sp_” in the stored procedure name.
- Use IF EXISTS (SELECT 1) instead of (SELECT *).
- Use the sp_executesql stored procedure instead of the EXECUTE statement.
- Try to avoid using SQL Server cursors whenever possible.
- Keep the Transaction as short as possible.
- Use TRY-Catch for error handling.