SQL Server PROCEDURE Variable Declaration

Question

I got a doubt why we have to reassign the variable to new value when we are calling a stored procedure with parameter. In procedure we need 2 variables. and that variables need to be reassigned for using inside the procedure.

Answer 1

Parameter sniffing

https://blogs.msdn.microsoft.com/turgays/2013/09/10/parameter-sniffing-problem-and-possible-workarounds/

SQL Server compiles the stored procedures using (sniffing) the parameters send the first time the procedure is compiled and put it in plan cache. After that every time the procedure executed again, SQL Server retrieves the execution plan from the cache and uses it (unless there is a reason for recompilation). The potential problem arises when the first time the stored procedure is executed, the set of parameters generate an acceptable plan for that set of parameters but very bad for other more common sets of parameters.

One of the solutions in the link provided is to use local variables. E.g what you are already doing.