I have a question about the dynamic SQL block of code you have included in your article.

So the way you have constructed your dynamic SQL, you took @FirstName and @LastName and converted them to their actual values and included them in your @SQL variable.

Shouldn't you rather have used @FirstName and @LAstNAme as parameters in @SQL so that the query execution plan will be reused for all combinations of @FirstName and @LAstNAme ? Otherwise, each time that a new value is passed for @FirstName and @LastNAme, a new execution plan will be generated.

What I mean is this..

Instead of :

[font="Courier New"]Set @SQL = 'select....where FirstNAme=' + Quotename(@firstname,'''') + ' and LastName=' + QuoteName(@lastname,'''')[/font]

Use:

[font="Courier New"]Set @SQL = 'Select ... Where FirstName=@FirstName And LastName=@LastName'

and pass the parameters for @FirstName and @LastName[/font]