Venkatesh Kopula Sivakumar Venkatesh Kopula Sivakumar - 3 years ago 277
SQL Question

SQL Server PROCEDURE Variable Declaration

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 Source

Parameter sniffing

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download