To start with, a small stored procedure that I use all the time for bad parameter sniffing demos that reliably gets different plans with different values due to statistics skew:
PROC dbo.spAddressByCity @City NVARCHAR(30)
a.StateProvinceID = sp.StateProvinceID
a.City = @City;
If this procedure is called for a value of ‘London’ it gets a plan with a Merge Join. For most other value it gets a plan with a Loops Join. Here’s an example of the ‘London’ plan:
It’s a good question with a good answer.