SQL Server Parameter Sniffing issue - resolved

If you have a performance issue while calling the stored proc from ASP.net apps or SSRS reports then try this tip suggested by Chris on SQL parameter siffing fix.

Create PROC SP_NAME @param1 nvarchar(20)
AS
BEGIN
Select * from Customers where CustomerID = @param1
END

*** Declaring a local variable within stored proc and passing it to SQL just improves the performance of the execution of the stored proc while calling from .net applications.

Create PROC SP_NAME @param1 nvarchar(20)
AS

BEGIN
Declare @localvariable nvarchar(20)
SET @localvariable = @param1
Select * from Customers where CustomerID = @localvariable
END

http://elegantcode.com/2009/06/24/repost-watch-out-for-sql-server-parameter-sniffing/