How to optimize a Stored Procedure or SQL Query?

Use SET NOCOUNT ON

Specify column names instead of using * .

SELECT * FROM table1        
SELECT col1, col2 FROM table1

Use schema name before objects or table names.
    SELECT EmpID, Name FROM Employee
    SELECT EmpID, Name FROM dbo.Employee

Use EXISTS () instead of COUNT ().
    SELECT Count(1) FROM dbo.Employee
    IF( EXISTS (SELECT 1 FROM db.Employees))

Use TRANSACTION when required only.
Do not use DYNAMIC QUERIES. They are vulnerable to SQL Injections.