I find them usefull but not as much as they were with Classic ASP.
Now I'm using them more locally with triggers in packages that pump to and from remote servers.
Horses for courses.
All of our 250+ reports use stored procedures to pull the data from the database (into DataSets which are then attached to the reports). So when the customer asks for changes to calculations we can often just supply them with a script to alter the stored proc
rather than an entire release of the software.
For other processes, we might use a stored procedure for bulk updates (we have some annual data updates that need to occur for every customer account in the system, for example).
For performance, we might consider converting complex data manipulations into stored procedures to reduce the 'shuffling about' of data from the DB server to our app server on a different machine and back again.