locked
Stored proc vs UDF RRS feed

  • Question

  • I am of the opinion that if you are writing some sql that could be placed in either a stored proc or a UDF then you are better off putting it in a UDF. Why? Because the UDF can then be used in other select statements. I also find it makes things a little clearer in that stored procs are then generally only for updating data and functions return data. I'm just interested in peoples opinion on this. I tried searching but everything I found just pointed out the difference between the 2

    Thanks in advance,
    Michael

    Friday, March 23, 2012 12:38 AM

Answers

  • As with most situations, the answer is "it depends".  Some of the issues:

    • Will the data returned be joined in queries?
    • Can the abstraction be made as an inline function?
    • Will abstraction ever be used to fetch part of the data rather than all of the data?
      If so, can join elimination potentially be a performance boost for the queries?

    I tend to use inline functions in situations where I might otherwise use a view other than the need for parameters.  Also, don't eliminate views from consideration.  The advantage views have over either procedures or functions is that many views can be indexed to provide additional performance not available either to an inline function or a procedure -- at least not from the perspective of abstraction as a single object.


    • Edited by Kent Waldrop Friday, March 23, 2012 2:31 PM
    • Proposed as answer by Peja TaoEditor Monday, March 26, 2012 8:05 AM
    • Marked as answer by MickleKulls Monday, March 26, 2012 7:40 PM
    Friday, March 23, 2012 2:25 PM

All replies

  • If you can create this as inline UDF, then, perhaps, having it as UDF vs. stored procedure can be beneficial. If the SP is complex, multi-line processing, then keeping it as stored procedure is better for performance reasons.

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


    My blog

    Friday, March 23, 2012 12:40 AM
    Answerer
  • The simplest general definition I can think of is:

    A function always returns a value while a procedure just executes commands.



    Friday, March 23, 2012 12:50 AM
  • A UDF has a RETURN and can be CALLed inline. SPs cannot. Otherwise, they are pretty much identical.

    I am of a different opinon regarding code pacement, however. TABLEs are for objects, VIEWs are for pseudo-objects, usually based on business logic or common connections in the data.  In either case, VIEWs canonize the logic for the pseudo-object. PROCEDUREs are for APIs or SOPs (Standard Operating Porcedure), that is, to define a specific set of steps, in a single location, for an action. FUNCTIONs are for data conversions, such as converting to radians, applying a cryptological key, or choosing a radom number.

    In practice, i rarely define FUNCTIONs, i use PROCEDUREs, and i often CREATE VIEWs.

    Friday, March 23, 2012 12:34 PM
    Answerer
  • As with most situations, the answer is "it depends".  Some of the issues:

    • Will the data returned be joined in queries?
    • Can the abstraction be made as an inline function?
    • Will abstraction ever be used to fetch part of the data rather than all of the data?
      If so, can join elimination potentially be a performance boost for the queries?

    I tend to use inline functions in situations where I might otherwise use a view other than the need for parameters.  Also, don't eliminate views from consideration.  The advantage views have over either procedures or functions is that many views can be indexed to provide additional performance not available either to an inline function or a procedure -- at least not from the perspective of abstraction as a single object.


    • Edited by Kent Waldrop Friday, March 23, 2012 2:31 PM
    • Proposed as answer by Peja TaoEditor Monday, March 26, 2012 8:05 AM
    • Marked as answer by MickleKulls Monday, March 26, 2012 7:40 PM
    Friday, March 23, 2012 2:25 PM
  • I can see a good reason to not use a function. Say we have a query that is written specifially for one report. It is possible that someone could grab that query and join to it but then that could make it more difficult to modify your query in the future. By making it a stored proc you are avoiding that possibility.
    Monday, March 26, 2012 7:45 PM
  • It may be beneficial to use a view for such scenarios. Just don't try to nest the views.

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


    My blog

    Monday, March 26, 2012 8:21 PM
    Answerer
  • What is the issue with nesting views? Is it a problem for performance?
    Wednesday, March 28, 2012 10:02 PM
  • Yes. Check this post

    T-SQL Wednesday #21 – T-SQL That Should Have Been Flushed Down the Toilet


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


    My blog

    Wednesday, March 28, 2012 10:28 PM
    Answerer