Creating a SProc that returns multiple result sets versus separate SProcs

Question

Hi

I am looking for reasons to support my case that we should create separate stored procedures for each table we are returning data for an application as opposed to creating one stored procedure that returns results for any drop down table. My reasons for separating would include easier maintenance of individual result set changes without affecting all result sets and reduced need to recompiles. Can another provide any others or weigh in on the keeping them all in one SProc?

The background is that we have a form with many dropdown and the developer wrote one stored procedure that takes in a parameter and then returns the results as XML to the form.

Sample code:

CREATE PROCEDURE manyRS

(@type varchar(30))

AS

IF @type = 'First'

       SELECT *

       FROM First

ELSE

       IF @type = 'Second'

              SELECT *

              FROM Second

       ELSE

              IF @type = 'Third'

                     SELECT *

                     FROM Third

GO

TIA,

Dave

Answer 1

Using separate stored procedure for each case will make it easier for compiler and will be better for performance. The developer probably assumed that one stored procedure is easier to maintain than many. I think performance needs to be considered first.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

I have been working with stored procedures since the 80s.  One common mistake made by developers, even experienced developers, is to attempt to make stored procedures that are too generic.  Stored procedures that are too generic, and this looks like one that would be,  tend to suffer performance problems.  What can and does happen is that a bad query plan gets associated with a procedure that is too generic.  Once this bad plan gets in, subsequent calls to the procedure might use the wrong query plan and performance can suffer tremendously when the wrong plan gets used.

This is far less likely to happen if you make your procedures more specific.

I sure holpe Joe Celko weighs in on this.

Joe!   H E L P ! !

EDIT:

Here are some previous posts that discuss problems with code that is over-generic:

Generic (Over-generic) Problems:

   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/042150d9-9da8-4770-ac0f-7bde5c9d95d6/
   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/588e473b-4c63-4fc7-9406-5a7499a3adbc/
      Umachandar Jayachandran

   http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/1ca2f3ba-55ed-4fcb-84bf-ba40e23d38b7
      Umachandar Jayachandran
      Hugo Kornelis

   http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/3df14960-2887-46cd-8c46-53b0608a68dc
      Umachandar Jayachandran
      K H Tran
      S Hughes

   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/b5aecf73-7066-4391-990a-e2d7fd3e1d82/
   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/f112f1eb-03a7-436e-921d-1ac428bdab0d/
   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/e2d75661-a51a-43ed-a117-b3c1ef990360/
      Louis Davidson

   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/49eba1d6-afb7-4fbf-b233-39fa0401365b/
      Louis Davidson
      Umachandar Jayachandran

   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/b1f44b9f-9f50-4f9e-a355-f098000b1c79/
      Louis Davidson
      Jens Suessmeyer

   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/3afda18e-6b7f-4e0a-9f09-b4d4bf2a1a1b/
      Louis Davidson
      Arnie Rowland

Answer 3

Thanks for the great reference material and answer!

Cna I assume your answer is the same for UDFs as SProcs?  For example, a UDF that returns XML but based on one of numerous SELECT statements.

Answer 4

The answer will be the same. 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

In general, yes; however, there are some other things to consider with functions:

First, scalar functions at the present time are best avoided.  This is because scalar functions add a lot of drag to the query.  For simple functions that can otherwise be written as an inline calculation it is best to do this as an inline calculation rather than a scalar function.  Also, if the scalar function is complex but can otherwise be expressed as an inline table function then this alternate abstraction form can provide better performance than scalar functions in many cases.  If the scalar function is complicated and really needs to be abstracted as a scalar function, then go ahead and do so; just realize that there will be a performance hit.  At issue is that the scalar function gets called for each row that uses it; that can add up to a lot of additional overhead!

The same can be true of a multi-line table function if it is joined to another table or view: sometimes these functions also get called for each row processed.  That can be a massive performance problem if the multiline table function is doing a lot of work.

The best performing function abstractions are the inline table functions.  These are optimized similarly to the way views are optimized.

Here are some previous posts related to some of the problems associated with the performance of functions -- especially scalar functions:

   http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/1f35d3b7-c1a6-4489-ad67-b785e3105d0f
      Kent Waldrop
      Erland Sommarskog
      Vina Valeti

 * http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/2929ce2c-0c67-420d-867b-841af5134028/
   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/8f762143-9781-46d1-85f1-ad2fa250ad3e/
   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/1a4a972a-5da6-438f-8af5-8b01419f7438/
      Umachandar Jayachandran

   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/067b11c1-fb78-4417-9921-acf280c15a03/
   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/2c788ad1-463a-4f78-9409-b89a3e24934e/
   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/0cd8d3f3-aceb-454c-bb55-4395bac1ad33/
   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/d66ad0f6-0aba-4656-b979-598c2a05e8d4/
   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/7af7ffce-1e9d-424d-a294-194c838a0852/
   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/a50847a2-5e9d-49d5-8d1f-ecaf4651d286/
   http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/255c4d2d-6ea2-4d2e-9663-19e6612b69f5
      Kent Waldrop