Personally, I write and use SPROCs in all of my
ASP.NET/WinForms + MSDE/SQL 2000 projects extensively, and I
saw lots of
discussion/blogsabout
Dynamic SQL, SPROCs, Ad hoc SQL script... everywhere.. and
now, VS Developer Center provide a pretty good (short and
neat) article of SPROCs for .NET developers. Check it out:
Evaluation of Stored Procedures for the .NET Developer