I am a fan of stored procedures but as you know with everything advantages as well as disadvantages are attached.
In my post article on advantages i have mentioned the best advantage of
stored procedure is that they can retain the execution plan in plan
cache and can reuse the plan for executing the stored procedure.

Lets go through the disadvantages of stored procedures.All the below
points are my personal opinion.Community is intelligent enough to
understand the things when to use what.Also what ever is given below is
depends on the situations.(It depends)

1.Stored procedures are not faster than dynamic SQL’s.

2.No version control system is available for Stored prcedures.We can
find out created date and last modified date but we cannot find the
complete history for the same.

3.Stored procedures are concrete.It is very difficult to change them
as they are written by the experienced developers and not all persons in
the project have rights to modify the same.

4.Business logic in stored procedures.Business logic should be written in the business layer not at the database side.

5.Debugging is very difficult in case of stored procedures.Although
we can debug stored procedures with the help of visual studio.

there could be many more disadvatages for the same.Please share your knowledge as we all here to learn.

Pawan Kumar