I have read much on both sides of this debate: is there a signficant performance gain to be had by using only stored procedures over raw queries? I am specifically interested in SQL Server but would be interested in any and all databases.
It is less so in SQL Server 2008 and higher, but it is still there. What it comes down to is the execution plan cache and SQL Server being able to auto-parametrize queries which are sent in. When using stored procedures (that don't have dynamic SQL within them) the queries are already parametrized so SQL Server doesn't need to generate a plan for each query when it's run as the plans are already stored in the plan cache.
And don't forget about the security issues (dynamic SQL, minimum permissions, etc.) that go away when using stored procedures.
When the app is using dynamic SQL against the base tables to select, insert, update and delete the data in the tables the application needs to have rights to all those objects directly. So if someone uses SQL Injection to get onto the server they will have rights to query, change or delete all the data in those tables.
If you are using stored procedures they only have the rights to execute the stored procedures getting back just the information which the stored procedure would return. Instead of issuing a quick delete statement and blowing everything away they would need to figure out what procedures can be used to delete data then figure out how to use the procedure to do so.
Given that SQL Injection is the easiest way to break into a database, this is kind of important.
As an addendum to Denny's answer, it's not uncommon to find systems where significant buffer pool memory is wasted on single or low use ad-hoc execution plans, created as a result of queries being used over procs.
Worst cases lately, 8GB allocated to an instance, 3GB plan cache, 2.5GB single use plans. The majority of these have been SQL2005 so it hasn't been an option to try the optimise for ad-hoc workloads setting.
It's certainly getting more difficult to include performance in a justification for procedures over raw queries. One of the strongest arguments for me now is "If you use procedures, it's far easier for me to help when performance issues arise". A dynamic/linq/orm interface doesn't prevent you from tuning, but it can severely limit your options.
SQL Server caches and optimizes stored procedures and ad-hoc SQL in the same way. For example, this procedure:
create procedure dbo.TestSB(@id int) as select * from Orders where id = @id
Will be optimzed and cached identically to:
select * from Orders where id = @id
However, the following ad-hoc SQL cannot be cached effectively, because of the hardcoded value:
select * from Orders where id = 42
Although performance is the same, there are good reasons to use stored procedures. Stored procedures provide a clear separation between DBA and application developers. It's good to have an extra layer of defense between your valuable data and constantly changing programs :)