SQL Stored Procedure Parameter sniffing

I came across this by accident.  I was having issues with queries taking forever to return results.  Turns out, that if you pass parameters into a SP and use them in more than one place in the query, it will slow down the results returned to the user.

So, here's what you do...

• Define your parameters as usual
• At the the very beginning of the SP, declare a variable for each parameter making sure they have the same data type.
• Set each of the declared variables equal to the matching parameter.
• Use the declared variables in the query where you would usually use the parameters.

You will be amazed at the speed increase.