I am writing stored procedures for several operations in a database, I just wanted to know:
Which approach would be better?
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.
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.
每个过程只进行一次操作。
Don t you love Uncle Bob: Single responsibility principle
最好为不同的逻辑实施不同的过程。
这样，您可以轻松管理代码并减少依赖关系。
