Quite the debate going on from today's editorial: Stored Procedures Reconsidered. I chimed in late, and going on the "pro-sprocs" group. I think stored procedures are helpful, and a better idea, and most importantly:
Don't take that much more effort
You should be able to auto-gen trivial procs, and you should be able to auto-gen the stored procedure calls from .NET. Actually that's a little utility that we've considered writing before. Something you can point at a server, give it a proc name, have it sniff out the procedure parameters, types, etc. and write the code. How hard can that be?
I don't think that stored procedures are required everywhere, but I think they do provide benefits and ignoring those benefits because you don't want to write them (laziness) just doesn't make sense.