Difference between a stored procedure and function

  • Article

Benefits of Stored Procedures

  • Precompiled execution: SQL Server compiles each Stored Procedure once and then re utilizes the execution plan. This results in tremendous performance boosts when Stored Procedures are called repeatedly.
  • Reduced client/server traffic: If network bandwidth is a concern in your environment then you'll be happy to learn that Stored Procedures can reduce long SQL queries to a single line that is transmitted over the wire.
  • Efficient reuse of code and programming abstraction: Stored Procedures can be used by multiple users and client programs. If you utilize them in a planned manner then you'll find the development cycle requires less time.
  • Enhanced security controls: You can grant users permission to execute a Stored Procedure independently of underlying table permissions.

Benefits of User Defined Functions

  • They allow modular programming: You can create the function once, store it in the database, and call it any number of times in your program. User Defined Functions can be modified independently of the program source code.
  • They allow faster execution: Similar to Stored Procedures, Transact-SQL User Defined Functions reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated executions. This means the user-defined function does not need to be reparsed and reoptimized with each use resulting in much faster execution times. CLR functions offer significant performance advantage over Transact-SQL functions for computational tasks, string manipulation, and business logic. Transact-SQL functions are better suited for data-access intensive logic.
  • They can reduce network traffic: An operation that filters data based on some complex constraint that cannot be expressed in a single scalar expression can be expressed as a function. The function can then invoked in the WHERE clause to reduce the number or rows sent to the client.
Sr.No. User Defined Function Stored Procedure
1  Function must return a value. Stored Procedure may or not return values.
2 Will allow only Select statements, it will not allow us to use DML statements. Can have select statements as well as DML statements such as insert, update, delete and so on
3  It will allow only input parameters, doesn't support output parameters. It can have both input and output parameters.
4 It will not allow us to use try-catch blocks. For exception handling we can use try catch blocks.
5 Transactions are not allowed within functions. Can use transactions within Stored Procedures.
6 We can use only table variables, it will not allow using temporary tables. Can use both table variables as well as temporary table in it.
7 Stored Procedures can't be called from a function. Stored Procedures can call functions.
8  Functions can be called from a select statement. Procedures can't be called from Select/Where/Having and so on statements. Execute/Exec statement can be used to call/execute Stored Procedure.
9 A UDF can be used in join clause as a result set. Procedures can't be used in Join clause

Comments

  • Anonymous
    October 08, 2014
    Not all UDFs cache plan and reduce network traffic. Scalar and multitable functions can be detrimental if called with a resultset involving thousands of rows.

  • Anonymous
    October 08, 2014
    nice

  • Anonymous
    November 01, 2014
    Thanks for this short but easily understandable post. Your comparison of the benefits between UDF and Stored procedures is useful to try to make the "good" choice according the situation.

  • Anonymous
    July 21, 2015
    This block is very helpfull to understand differance betwen Function & Store procedure. it'll be helpful if some examples is added in the block.

  • Anonymous
    October 22, 2015
    This information is good but you can add some more examples to this information that will be helpfull to easy understanding.Thank you.

  • Anonymous
    November 01, 2015
    The comment has been removed

  • Anonymous
    December 16, 2015
    Nice Post, but you can more elaborate the article by describing some more related information regarding function and stored procedures, as i see here: www.sqlserverlogexplorer.com/functions-and-stored-procedures

  • Anonymous
    February 05, 2016
    Nice written article, well tried to explain the terms in short. After a long search I found a blog written on same that gets a closer look over function and stored procedure. sqltechtips.blogspot.in/.../closer-look-function-stored-procedure.html