Bert Wagner takes on the idea that stored procedures are faster (or slower) than ad hoc SQL:
A few months ago I was presenting for a user group when someone asked the following question:
Does a query embedded in a stored procedure execute faster than that same query submitted to SQL Server as a stand alone statement?
The room was pretty evenly split on the answer: some thought the stored procedures will always perform faster while others thought it wouldn’t really matter.
Bert explains the answer. For me, performance isn’t even on the radar for how I try to convince people to use procedures. Instead, explain it to developers as an interface: developers program against a contract, where they can send in a specified set of inputs (procedure parameters), get back a specified output (the shape of the result set), and not have to care about the fine details. Programming to interfaces is extremely common in business development, and this is just one more interface.