If we want to do certain repetitive tasks/operations over our database within the same application and database, then in this case the most useful method for this functioning is none other than Stored Procedures.
It is most oftenly called a "S-Proc"or "SP".
Difference between Function and Stored Procedure:
1.Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
SP: Stored in database in compiled format.
Note: Compiled indicates, Execution plan will be made by sql at the time it created and stored in DB.
Function: Will compiled at run time
SP: t can directly return only integers
Return type is not must
Function: It can return any scalar or table
Return type is must
Sp: Can have Try....Catch
Function: Can not have Try....Catch