UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be. UDFs that return tables can be treated as another row-set. This can be used in Join’s with other tables. Inline UDF’s can be thought of as views that take parameters and can be used in Join’s and other Row-set operations.
- Functions are compiled and executed at run time. Stored procedures are stored in parsed and compiled format in the database.
- Functions cannot affect the state of the database which means we cannot perform insert,delete,update and create operations on the database. Stored Procedures can affect the state of the database by using insert,delete,update and create operations.
- Functions are basically used to compute values. We passes some parameters to functions as input and then it performs some operations on the parameter and return output. Stored procedures are basically used to process the task.
- Function can not change server environment and our operating system environment. Stored procedures can change server environment and our operating system environment.
- Functions can run an executable file from SQL SELECT or an action query. Stored Procedure use Execute or Exec to run.