Stored Procedure
A Stored Procedure is a program (or procedure) which is physically stored within a database. The advantage of a stored procedure is that when it is run, in response to a user request, it is run directly by the database engine, which usually runs on a separate database server. As such, it has direct access to the data it needs to manipulate and only needs to send its results back to the user, doing away with the overhead of communicating large amounts of data back and forth.
User-defined Function
A user-defined function is a routine that encapsulates useful logic for use in other queries. While views are limited to a single
Differences
*. SP can return zero or n values whereas function can return one value which is mandatory.
*. SP can have input/output parameters for it whereas functions can have only input parameters.
*. SP allows select as well as DML statement in it whereas function allows only select statement in it.
*. Functions can be called from procedure whereas procedures cannot be called from function.
*. 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.
*. SP can not be utilized in a select statement whereas function can be embedded in a select statement.
A Stored Procedure is a program (or procedure) which is physically stored within a database. The advantage of a stored procedure is that when it is run, in response to a user request, it is run directly by the database engine, which usually runs on a separate database server. As such, it has direct access to the data it needs to manipulate and only needs to send its results back to the user, doing away with the overhead of communicating large amounts of data back and forth.
User-defined Function
A user-defined function is a routine that encapsulates useful logic for use in other queries. While views are limited to a single
SELECT statement, user-defined functions
can have multiple SELECT
statements and provide more powerful logic than is possible with views.Differences
*. SP can return zero or n values whereas function can return one value which is mandatory.
*. SP can have input/output parameters for it whereas functions can have only input parameters.
*. SP allows select as well as DML statement in it whereas function allows only select statement in it.
*. Functions can be called from procedure whereas procedures cannot be called from function.
*. 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.
*. SP can not be utilized in a select statement whereas function can be embedded in a select statement.
