What are the disadvantage of Stored Procedure?

Disadvantages

• Applications that involve extensive business logic and processing could place an excessive load on the server if the logic was implemented entirely in stored procedures. Examples of this type of processing include data transfers, data traversals, data transformations and intensive computational operations. You should move this type of processing to business process or data access logic components, which are a more scalable resource than your database server.
• Do not put all of your business logic into stored procedures. Maintenance and the agility of your application becomes an issue when you must modify business logic in T-SQL. For example, ISV applications that support multiple RDBMS should not need to maintain separate stored procedures for each system.
• Writing and maintaining stored procedures is most often a specialized skill set that not all developers possess. This situation may introduce bottlenecks in the project development schedule.

http://kalitinterviewquestions.blogspot.com/

From a deployment point of view, Stored procs disadvantage for me, is that they can be modified by the end-user which can lead to unpredictable results in your application.  You can protect against this a bit by encrypting them and jumping through some other hoops, but all of that is extra hassle and take away from one of the main advantages of them which is that they can be easily modified without changing the application source-code.