Difference between Stored Procedure and Function in SQL Server

Tram Ho

1. Introduction

  • Both stored procedures and functions are database objects that contain a set of SQL statements to complete a task.
  • A stored procedure can be reused many times. So if you have an SQL query that you intend to use multiple times then save it as a stored procedure, then just call it to execute your SQL query. Additionally, you can also pass parameters to a stored procedure …
  • A function is compiled and executed every time the function is called. The function must return a value …

To compare the difference between Stored Procedure and Function, you must have a fairly good knowledge of SQL.

2. The basic difference between Stored Procedure and Function

  • The stored procedure may return zero, one or more values. While the function must return a unique value (possibly a table).
  • Functions can only have input parameters for it, while a stored procedure can have input or output parameters.
  • Functions can be called from stored procedures while stored procedures cannot be called from functions.

3. Advanced differences between Stored Procedure and Function

  • Procedures cannot be used in the SELECT statement while the function can be embedded in the SELECT statement. Because a procedure can return multiple result sets, it is not suitable for use in the SELECT statement.
  • Stored procedures cannot be used in WHERE / HAVING / SELECT statements while functions can.
  • An exception can be handled by try-catch in the stored procedure, which is not possible with the function.
  • Transactions can be used in stored procedures, with functions not possible.

4. References

  • This article I have shared with you some differences between Stored Procedure and Function. Hopefully the article will bring some valuable information to you.
  • You can refer to some additional documents: Stored Procedure , Function
Share the news now

Source : Viblo