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.