To SP or not to SP in SQL Server

There have been a number of discussions about whether to use SPs or not, often shedding more heat than light,...