Query execution order in SQL Server database engine

Reading Output statistics for SQL SERVER

I have lot of questions in my mind while start optimization on procedures. On of the question is how can we see what happens with SQL SERVER when a procedure executes? How system calculates overall procedure execution time? and many others.

I have found many published material on these topics and most suitable command to read Statistics for procedure is as below

This command will show you detail about

• Execution time
• Logical reads
• Physical reads
• read-ahead reads
• Scan Count
• CPU time

and other options you need to note for excessive query optimization.

SET Statistics IO ON
SET Statistics TIME ON

exec procedurename "parameters...."

SET Statistics IO OFF
SET Statistics TIME OFF

Using Case in where clause utilizing parameter

Recently i have faced a problem about parameters usage in where clase. Problem statement is as?
"I have 2 parameters and all alow null values. How can i impliement this logic."

I got one solution that is as under:

For 2 parameters

@vparam1
@vparam2

if @vparam1 is not null and @vparam2 is not null
Select "column list..."
From "table..."
Where "columname" = @vparam1 and "columnname" = @vparam2
elseif @vparam1 is not null and @vparam2 is null
Select "column list..."
From "table..."
Where "columname" = @vparam1
elseif @vparam2 is not null and @vparam1 is null
Select "column list..."
From "table..."
Where "columname" = @vparam2
else
Select "column list..."
From "table..."

If any one have another solution please update me ...... in any version of SQL Server