Excellent article Brian. It is one of the best written articles I have seen on stored procedure execution on the server.
One issue I was wondering if you'd look at is a stored procedure that runs two different types of selects such as:
create procedure MyProc
@id1 int = null, @id2 int = null
as
if @id1 is null
select * from MyTable where id2=@id2
else
select * from MyTable where id1=@id1
return
Does this cause a recompile if different parameters are used?
Steve Jones