Should I use a single procedure for multiple operations, or split them into multiple procedures?

Question

I am writing stored procedures for several operations in a database, I just wanted to know:

• Is it better to design a single procedure for a table which can perform add, modify, delete operations or...
• Should I design seperate procedures for each operations

Which approach would be better?

Answer 1

I would say that a separate stored procedure for each operation is best. Otherwise you get too much logic inside your procedures.

Besides, most operations require different parameters. For a delete you only need the primary key but for an insert you must provide values all not nullable columns. You do not want to provide dummy insert parameters when you just want to delete a record.

Answer 2

At a minimum you'd need several SQL commands with if ... else logic. Stored procedures are, I find, much easier to manage if they do exactly one thing.

It's a transferable concept from normal procedural development, but it applies even more strongly to stored procedures.

Benefits: Easier to test, more meaningful SP names (e.g. UpdateCustomer rather than CustomerHandler), less coupling/better cohesion, simpler procedures, fewer arguments.

Answer 3

One operation per procedure.

Answer 4

Don't you love Uncle Bob: Single responsibility principle

Answer 5

yes it is best to different procedures for different logics.we can easy manage code and reduce dependencies