[chris_wot]

Another thing is that he doesn't need to use a stored procedure, he could use prepared sql statements.

In regards to your last sentence, he could change the isolation mode if he didn't care so much about consistency.

[orcadk]

He already is using prepared statements, that happens under the hood when you use parameterized queries. Problem is the lack of type definition (in the first revision), causing a new compilation for each length of the varchar column.

[chris_wot]

I'm not following... Could you explain a little further? My understanding is that prepared statements will be added to the plan cache, in the same way that stored procs have associated plans for each query on the proc.

One warning about this is that at times you should consider having a new plan generated - especially if the data changes over time.