In theory, the pros and cons are as so:

Pros:

1. One place to contain all of the business logic
2. Possibly faster applications because multiple SQL queries and such can be performed in one “round trip” to the database
3. Trivial to make use of the stored procedures from multiple applications

Cons:

1. A DBA will be required for performance tuning
2. All developers will have to be very well versed in your particular SQL dialect(T-SQL, Pl/SQL, etc)
3. SQL code isn’t as expressive and thus harder to write when covering higher level concepts that aren’t really related to data
4. A lot more unnecessary load on the database

Now, practically, only a fool would have all business logic in the database.

1. Very few developers will be able to create a consistent stored procedure interface that works easily across applications. Usually this is because certain assumptions are made of that calling application
2. Same goes for documenting all of those stored procedures
3. Database servers are generally bottlenecked enough as it is. Putting the unnecessary load on them just further narrows this bottleneck. Intricate load balancing and beefy hardware will be required for anything with a decent amount of traffic
4. SQL is just barely a programming language. I once had the pleasure of maintaining a scripting engine written as a T-SQL stored procedure. It was slow, nearly impossible to understand, and took days to implement what would have been a trivial extension in most languages
5. What happens when you have a client that needs their database to run a different SQL server? You’ll basically have to start from scratch — You’re very tied to your database. Same goes for when Microsoft decides to deprecate a few functions you use a couple hundred times across your stored procedures
6. Source control is extremely difficult to do properly with stored procedures, more so when you have a lot of them
7. Databases are hard to keep in sync. What about when you have a conflict of some sort between 2 developers that are working in the database at the same time? They’ll be overwriting each others code not really aware of it, depending on your “development database” setup
8. The tools are definitely less easy to work with, no matter which database engine you use.

So, to objectively answer the question. In most cases, stored procedures are only needed in some cases. For instance, if you have a report to generate where you need to do a lot of conditional processing across a couple of big tables, you wouldn’t want your application to be making a couple hundred SQL queries to the database. You’d want to make a stored procedure so you didn’t have the network lag overhead. And, you’ll usually only want to make stored procedures otherwise for when clients want to run a custom-ish query across your database. Stored procedures and views can make this easily possible without your clients being a database whiz.

Cons:

• The database is the least scalable part of your architecture.
• Especially in an age of web services and REST-ful 3rd party web applications, you won’t be able to contain all of your business logic in stored procs.
• SQL implementations vary, some can be tricky, and none read as fluidly as a first-class application-layer programming language.
• Remember that code is read much more often than it is written.

Pros of holding all the business logic on stored procedures in web application
for:

• One place is easier to maintain than multiple
• Databases are fast and optimized and can be scaled well.
• The same procedures can be called from mutiple, different frameworks and languages.
• The logic is decoupled from the implementation in particular applications.
• Rework may be reduced for changing applications when the database stays the same.

Cons of holding all the business logic on stored procedures in web application:
against:

• Good SQL knowledge can be hard to find in many locations.
• Good SQL coders can be expensive.
• All application developers will need to be able to either change it or request a quick change.
• Some data that is less well suitable to a SQL database, e.g. image data or document data may not be available in the database and it may be hard to integrate it with the stored procedures..

There are 2 big cons that I usually encounter:

1. Inability to unit test your stored procedures. Sure there are some unit test frameworks out there, but you usually run into issues of running more than 1 set of tests at once (concurrency and transaction troubles).
2. Most companies will want to put a lot of login in to stored procs, but then hire 5x more application programmers (be it C#, Ruby, Java, whatever) than database programmers or DBAs, and for some reason expect that all their app coders should fully understand and be able to write performant, scalable database procedures (in PL/SQL, T-SQL, or whatever). Management usually doesn’t realize that doing that is a lot like hiring a C# expert to write you some Python code, and wondering why they aren’t writing amazing applications.

It depends

It all depends on your team's experience and your project's requirements. Bring in a DBA to your team and decide what should you do to meet the requirements.However, with multitier application design it might not be the good decision to encapsulate business logic in stored procedure.

It will NOT matter

As long as business logic:

• lives in one place
• where it is properly documented
• proper access is provided through services that can be loosely coupled
• through a published abstracted interface

I think that business logic in programming space makes more sense when Power of Expression is important in your team/project.

I belief that SQL space is not as expressive, but it can do the job. Use the best tools you have on hand for the most appropriate tasks. Fiddling with logic and higher order concepts is best done at the highest level. Consequently, storage and mass data manipulation is best done at the server level, probably in stored procedures.

In the modern age this isn’t really a common approach anymore. There was a time that putting business logic in stored procedures was a common thing to do early in web programming because the alternatives where few and often difficult to use. Today I would recommend against it.

I would have to say in 2012 there is nothing about business logic in stored procedures that could be justified as somehow a choice having beat out other approaches after having weighed the pro’s and cons’s. Any PRO points would be academic at best.

CON’s
It’s not linq code. The future of TSQL is compiled linq, mine as well start to learn it.

Compiled code is more stable and easier to work with. If I’m debugging something there is so much information available if I used C# or Java.

Stored procedures are likely to end up with too many responsibilities, it’s a best practice to try to keep your responsibilities to 1.

Lack of separation of responsibilities will lead to an application that just gets harder and harder to work on and less and less stable as it’s core classes just get bigger and bigger.

If you like Microsoft Asp.Net MVC combined with Entity Framework Code First will allow you to rapidly build any application and you won’t need any stored procedures.