Best Practice: One Stored Proc that always returns all fields or different stored procedure for each field set needed?

Question

If I have a table with Field 1, Field 2, Field 3, Field 4 and for one instance need just Field 1 and Field 2, but another I need Field 3 and Field 4 and yet another need all of them... Is it better to have a SP for each combination I need or one SP that always returns them all?

Answer 1

I would just have one Stored Procedure as it will be easier to maintain.

Does it need to be a Stored Procedure? You could rewrite it as a View then simply select the columns that you need.

Answer 2

If network bandwidth and memory usage is more important than hours of work and project simplicity, then make a separate SP for each task. Otherwise there's no point. (the gains aren't that great, and are noticeable only when the rowset is extremely large, or there are a lot of simultaneous requests)

Answer 3

Very important question:

Writing many stored procs that run the same query will make you spend a lot of time documenting and apologising to future maintainers.

For every time anyone wants to introduce a change, they have to consider whether it should apply to all stored procs, or to some or to one only...

I would do only one stored proc.

Answer 4

I'd go for 1 single and if you experience any performance issue, start to create several

Answer 5

As a general rule it is good practice to select only the columns we need to serve a particular purpose. This is particularly true for tables which have:

• lots of columns
• LOB columns
• sensitive or restricted data

However, if we have a complicated system with lots of tables it is obviously impractical to build a separate stored procedure for each distinct query. In fact it is probably undesirable to do so. The resultant API would be overwhelming to use and a lot of effort to maintain.

The solutions are several and various, and really depend on the nature of the applications. Views can help, although they share some of the same maintenance issues. Dynamic SQL is another approach. We can write complicated procedures which return many differnet result sets depending on the input parameters. Heck, sometimes we can even write SQL statements in the actual application.

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