Ask Learn
Preview
Please sign in to use this experience.
Sign inThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Benefits of Stored Procedures
Benefits of User Defined Functions
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 |
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
Please sign in to use this experience.
Sign in