What are the pros and cons of stored procedures?
Any data errors in handling stored procedures are not generated until runtime….Portability –
| Advantages | Disadvantages |
|---|---|
| It is faster. | It is difficult to debug. |
| It is pre-compiled. | Need expert developer, since difficult to write code. |
| It reduces network traffic. | It is database dependent. |
| It is reusable. | It is non-portable. |
What are the pros and cons of storing procedures in a database?
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.
Can you hold business logic in stored Procs?
Stored procedures and views can make this easily possible without your clients being a database whiz. 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.
Can You unit test business logic in stored procedures?
You can unit test business logic when its in a business logic layer. If it’s completely separate the persistance operations can be mocked so you are testing the BL only. A stored proc is far more difficult to maintain/debug/unit test than e.g. linq and c#. Functional programming (DB Stored procedure) vs OOP.
When do you need to use a stored procedure?
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.