Stored Procedure: SP is a SQL entity which helps in achieving consistent logic across application. SQL is group of SQL-transact statement complied to one single execution plan or would say a pre-compiled object compiled to first time only. SPs having a single execution plan which is point to ponder which makes it differ from function. At the time when a stored procedure is created a partial execution plan is saved in system table.
While function is compiled and executed every time it is called.
Function: Function are quite different from SPs serve purposes like a function do in programming languages. Take inputs and do some specific calculation.
Major differences listed below:
# Function can have only input parameters, SP can have both input and output parameters.
# Function: always return a value In SP it is optional.
# One input parameter is mandatory wiht Functions.
# We can call function from Stored Procedures, calling SP from function is not possible.
# Function allow only SELECT statements In SPs we can do DML statements as well i.e. INSERT/UPDATE/DELETE.
# We can’t use transactions in Functions.
# We can use Funtions in SELECT statements.
# Exception handling i.e. try catch blocks is possible with SPs only.
# Tables return from function can be used with JOINS.