In this post, I will explain the difference between Function and Stored Procedure in SQL Server.
A stored procedure is a group of SQL statements that have been created and stored in the database. A stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. If we modify a stored procedure all the clients will get the updated stored procedure.
A function is a database object in SQL Server. Basically, it is a set of SQL statements that accepts only input parameters, perform actions and return the result. A function can return only a single value or a table. We can’t use a function to Insert, Update, Delete records in the database table(s).
Difference between Function and Stored Procedure
- A Function must return a value but in Stored Procedure it is optional ( Procedure can return zero or n values).
- Functions can have only input parameters for it whereas Procedures can have input/output parameters.
- Functions can be called from Procedure whereas Procedures cannot be called from Function.
- The procedure allows SELECT as well as DML (INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
- Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
- Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
- Functions that return tables can be treated as another row set. This can be used in JOINs with other tables.
- Inline Function can be thought of as views that take parameters and can be used in JOINs and another Row set operations.
- An exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a Function.
- We can go for Transaction Management in Procedure whereas we can't go in Function.