Gail, I really don't want to argue this point - but I am having a small problem here. I have a query where I use this construct and include the with recompile option, and the actual execution plans for different criteria all use index seeks where appropriate.

I have tested this multiple times and still see index seeks being selected for each plan with different parameters.

Now, I think I understand what is happening and why it works on my system - but I really need to setup a test. I just have not had time yet.

Can you see what happens if you modify the parameterization option from simple to forced? Does this make any difference at all? If not, then I really don't understand why I can get index seeks on this procedure and very good performance.

BTW - the tables I am accessing are:

PatientAccessLog 51,867,860 rows

AuditLog 198,028,731 rows