Execution Time difference between a SP and same code executed as a batch

Have you ever encountered situation when a stored
procedure runs more time than the same code executed as a batch
via Query Analyzer/SSMS?

This might be due to "Parameter Sniffing".
Now in English:
It`s relevant only to SPs with input parameters(s).
When a SP executed for a first time,SQL Server will
create optimized execution plan that reflects provided value
of the input parameter.
In other words SQL Server will create execution plan based
and optimized according to input parameter.
And SQL Server will use this generated plan for
every SP call.

Let`s suppose that the created execution plan uses a
Clustered Index Scan.The 'Clustered Index Scan' was
chosen according to statistics (taking into account the values
distribution of filtered column).
Now,consider situation when we provide different value for
the input parameter and 'Clustered Index Seek' can be used.
But 'Clustered Index Scan' will be used instead of 'seek',due
to existing execution plan.

In case of executing the same code in 'Query Analyzer',
SQL Server cannot know(sniff) the value of local variable,so
it will create execution plan according to average distribution
values.

There are 5 methods to fix this issue:
1) Use Local Variable instead of input.
2) Use Recompile option.
3) Use OPTIMIZE FOR query hint.
4) Use OPTION (RECOMPILE) for some statement(s) in your SP.
5) Use sp_create_plan_guide,in case you don`t have permissions
to change the SP.

How you can find out if SQL Server used a sniffed parameter?
If the parameter is sniffed, the information can be seen by setting
the following option before execution the SP.
set showplan_xml on.

and look for this in generated XML Showplan:

ParameterList>
ColumnReference Column="@p" ParameterCompiledValue="(XXXX)" />
ParameterList>

where XXXX specifies the initial parameter value that has been
used to build the execution plan.

See the following link,it contains some examples with very good
explanations:
http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html