When we need to use local variables inside stored procedures and use them as predicates in queries involving large tables, make sure that performance impact of the same is assessed correctly.
While we can’t completely avoid using local variables, we should test the respective SQL in 3 different ways to determine if there are significant performance issues due to local variables.
Test case 1
Use literal and test the query
Test case 2
Create a stored procedure with local variable definition and respetive SQL in body of the SP and test by running the stored procedure
Test case 3
Remove local variable and directly use the parameter passed to the stored procedure as a predicate for the query.
In usual scenarios, Test case 3 will perform better and most preferred.
More information at below URLs.