Yes, I too guess so....

I was trying to explain the following:

Say, i have a procedure proc

Create proc proc(@param bigint)

AS

declare @param1 bigint

Set @param1 = @param

Select <colums> From tab A

inner join tab2 B on A.<col> = B.<col1>

inner join tab3 c on b.<col> = c.<col1> where tab2=@param1

When i see the execution plan it uses tab3 for custerd scan

but if am not using the variable @param1, it uses clustered seek as below.

Create proc proc(@param bigint)

AS

--declare @param1 bigint

--Set @param1 = @param

Select <colums> From tab A

inner join tab2 B on A.<col> = B.<col1>

inner join tab3 c on b.<col> = c.<col1> where tab2=@param

Somewhere i read/watched vedios on parameter sniffing, there a resolution as setting the params to a variable within the procedure and use the variable for the further operation.

As I experienced an odd behaviour, I dont know why?will break the same?

Please let me wherei would have gone wrong. or is there someone experienced so.

Appreciate your feedbacks.