Developers at my company have this habit of putting multiple semi-related functions within a stored procedure, calling the appropriate one based on a passed-in parameter, e.g.:

if @function = 1

    truncate table holiday_list

if @function = 2

    -- huge SQL statement populating the holiday_list table 

Is this a bad practice? It seems to me that SQL Server would not create a good execution plan, as the code branches into two disparate paths (this is a simple example, though many times there will be 4 or 5 such branches, doing unrelated tasks except for sharing the same base table).

  • 1
    dba.stackexchange.com/questions/9835/… Seems like some good info here. – Jacob H Jun 6 '17 at 18:41
  • Have you looked at the execution plan with and without the "if" functions? – Jacob H Jun 6 '17 at 18:42
  • 2
    Personally, even if the execution plans are perfect, from a code maintenance and "followability" point of view, I would not have complete disparate action (clear vs. populate a table) in the same procedure. (Especially if the difference between clear and populate from the caller is an argument being 1 or 2; a lot harder to follow what's happening if trying to debug the caller....) – RDFozz Jun 6 '17 at 19:10
  • I'm not clear on whether an execution plan is formed separately for each distinct statement in the procedure One or other statement will be executed, depending on the parameter. I believe there is a level at which any change in the procedure will cause all stored plans for its execution to be discarded, but that may be not so important unless the procedure is run a lot (say, several times an hour), which it may be. The big statements could be wrapped in EXEC(N'...') so that they exist as separate batches, to get around that. – Robert Carnegie Jun 7 '17 at 1:10

I'd say while it may make sense and if the functions didn't cause explain plan issues it won't cause problems but not the best design to follow. Just because you can do something doesn't mean you really should. The possible repercussions of this design can bite you and could be a pain to hunt down the issue.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.