I have written my share of SQL Plumbing. I think I personally would be perfecticly happy to sac the perf by doing Stored procs in favor of granularity and speed of development. Its hard for example to write TSQL that only updates fields that have changed.
With SQL Linq this will no longer be the case. I nearly jumped up and down when I heard of Linq.