Stored Procedures are pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. But Function is compiled and executed every time when it is called. For more about stored procedure and function refer the articles Different types of Stored Procedure andDifferent types of Function.

Basic Difference

  1. Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).

  2. Functions can have only input parameters for it whereas Procedures can have input/output parameters .

  3. Functions can be called from Procedure whereas Procedures cannot be called from Function.

Advance Difference

  1. Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.

  2. Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.

  3. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.

  4. The most important feature of stored procedures over function is to retention and reuse the execution plan while in case of function it will be compiled every time.

  5. Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.

  6. Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

  7. Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.

  8. We can go for Transaction Management in Procedure whereas we can't go in Function.

Summary

In this article I try to explain the difference between Stored Procedure and Function. I hope after reading this article you will be able to understand BASIC as well as ADVANCE difference between Stored Procedure and Functions (UDF). Comments, criticism and suggestions are always welcome.

最新文章

  1. ExtJs基础知识总结：Dom、IFrame和TreePanel、TabPanel（三）
  2. Windows平台下利用APM来做负载均衡方案 - 负载均衡（下）
  3. C# Redis使用之StackExchange
  4. zabbix3.0报错记录
  5. JavaScript模块化开发整理
  6. 自定义控件之 TextBox
  7. linux后台进程管理工具supervisor
  8. Java之绘制实例
  9. (三)学习JavaScript之getElementsByTagName方法
  10. POJ_2503_Babelfish_(Trie/map)
  11. Linux 文件
  12. HBase 3、HBase练习题
  13. Html 经典布局（一）
  14. 【Unity Shaders】Transparency —— 使用渲染队列进行深度排序
  15. 【SVN】SVN初识
  16. 【linux】ubuntu中上下左右键变为^[[A^[[B^[[D^[[C问题处理
  17. python爬虫headers设置后无效解决方案
  18. [Algorithm] Search for matching words
  19. 洛谷P2692 覆盖 题解
  20. JZOJ.5288【NOIP2017模拟8.17】球场大佬

热门文章

  1. CDOJ 1437 谭松松的旅游计划 Label:倍增LCA && 最短路
  2. 【JAVA】Math.Round()函数常见问题“四舍5入”
  3. C程序演示产生僵死进程的过程
  4. ld: warning: directory not found for option 去掉警告的方法
  5. CentOS 6.7安装Mysql 5.7
  6. nodejs模仿http请求组件nodegrass简单例子
  7. 李洪强漫谈iOS开发[C语言-048]-打印平方表
  8. 使用C#向ACCESS中插入数据（仅供参考）
  9. [CareerCup] 17.6 Sort Array 排列数组
  10. Js分页插件，支持页面跳转