Differences between functions and stored procedures

Sangunni @ CodePoject posted a great article on the
differences between stored procedures and functions

To summarize (Stored Procedures = SP, User defined functions = UDF)

  • SP: Can return zero or n values
    UDF: Returns one value which is mandatory.
  • SP: Can have input/output parameters
    UDF: Can have only input parameters.
  • SP: Allows select & DML statements
    UDF: Allows only select statements (Note: Other logical operations possible)
  • SP: Can use functions within definition
    UDF: Cannot use SPs within definition
  • SP: Can exception handle via Try-Catch block
    UDF: Cannot use a Try-Catch block
  • SP: Transaction management possible
    UDF: No transactions
  • SP: Can not be utilized in a select statement
    UDF: Can be used in a select statement (and WHERE/HAVING)
  • UDF: Can be used in JOINs and other Rowset operations (if it returns a TABLE)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>