Best Practice: One Stored Proc that always returns all fields or another stored procedure for each required set of fields?

Question

If I have a table with field 1, field 2, field 3, field 4 and for one instance, I only need field 1 and field 2, but I need another field 3 and field 4, but I also need all this., Is it better to have An SP for every combination I need, or one SP that always returns all of them?

Answer 1

Very important question:

Writing many stored procedures that run the same request will force you to spend a lot of time documenting and apologizing to future maintainers.

, - , , ...

.

Answer 2

, .

? , .

Answer 3

, SP . . ( , )

Answer 4

, , . , :

• LOB

, , , . , , . API .

The solutions are somewhat different and really depend on the nature of the applications. Presentations can help, although they share some of the same service issues. Dynamic SQL is another approach. We can write complex procedures that return a lot of differnet result sets depending on the input parameters. Hell, sometimes we can even write SQL statements in a real application.

Oh, and there is a simple procedure that basically wraps SELECT * FROM some_table, but comes with its own set of problems.