They offer a clean and easy (IMHO) way of making sure I've got the exact same code for common jobs encapsulated and tracked within the database. One routine to add a user (for example) which is common to all client interfaces, handles all the exceptions in exactly the same way, tracks all the security in exactly the same way. I've had my share of problems where you get an odd bug because the app has subtly different SQL for the same job in different places, which just don't happen with stored procs.
In theory you can get this benefit from any common code repository and indeed, where SPs haven't been available, I have written code with a common SQL cache held outside the database. SPs have the advantages though of being incredibly easy to update - no need for a build and deploy, you can correct the code on the live server without disturbing it almost immediately if you need - and of doing so in a way that is then fully trackable by the database itself. I can easily query the information schema to find all procedures that reference a certain object that haven't been updated in the last week, for example.
They've also got security benefits. It's common practice in some areas to lock the tables down completely and restrict access to purely through SPs by permission, which can offer major benefits because you've got far greater control over what user accounts can do what to your database.
They're not perfect and I agree they're not portable (though in practice I've needed that very, very rarely) but I find the benefits hugely outweigh the downsides and will pull a face if told not to use them because it's simpler just to treat the database like a slightly funny version of Excel (or words to that effect, which I have had and don't get me started...!)
They offer a clean and easy (IMHO) way of making sure I've got the exact same code for common jobs encapsulated and tracked within the database. One routine to add a user (for example) which is common to all client interfaces, handles all the exceptions in exactly the same way, tracks all the security in exactly the same way. I've had my share of problems where you get an odd bug because the app has subtly different SQL for the same job in different places, which just don't happen with stored procs.
In theory you can get this benefit from any common code repository and indeed, where SPs haven't been available, I have written code with a common SQL cache held outside the database. SPs have the advantages though of being incredibly easy to update - no need for a build and deploy, you can correct the code on the live server without disturbing it almost immediately if you need - and of doing so in a way that is then fully trackable by the database itself. I can easily query the information schema to find all procedures that reference a certain object that haven't been updated in the last week, for example.
They've also got security benefits. It's common practice in some areas to lock the tables down completely and restrict access to purely through SPs by permission, which can offer major benefits because you've got far greater control over what user accounts can do what to your database.
They're not perfect and I agree they're not portable (though in practice I've needed that very, very rarely) but I find the benefits hugely outweigh the downsides and will pull a face if told not to use them because it's simpler just to treat the database like a slightly funny version of Excel (or words to that effect, which I have had and don't get me started...!)