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

Transaction Handling for SQL Server Procedures

--------------- SQL Server 2000

Begin Transaction
------- Paste your code here
------- On every Insert or Update write
if @@Error > 0
GoTo ErrorPoint

Commit Transaction

ErrorPoint: --- this is go to defination
Rollback Transaction

--------------- SQL Server 2005
Begin Try
Begin Transaction
----------- Paste your code here
Commit Transaction
End Try
Begin Catch
Rollback Transaction
End Catch

Database restore using T-SQL

If you find difficulty with SSMS to restore your database you can restore it with T-SQL as follows.

RESTORE DATABASE CC_DEV FROM DISK = 'C:\CC_DEV.bak' WITH MOVE CC_Data' TO 'c:\CC_Dev\CC_Data.mdf',MOVE 'CC_log' TO 'c:\CC_Dev\CC_Log.mdf'

This post will use for restore in all version of SQL SERVER