Differences between User Defined Functions and Stored Procedures

User Defined Functions

1 UDF are simpler to invoke than Stored Procedures from inside another SQL statement.
2 SP's are Complex to Invoke.
3 UDF's must be prefaced with the owner name, DBO in this case. Not mandatory.
4 When call function then the parameter must transmit full.
5 A UDF is always used to return a value or a table object.
6 Function return type could be scalar or table or table values(SQL Server).
7 Function returns only one value at a time
8 We can call the functions in SQL statements (select max(sal) from emp)
9 Function do not return the images, text.
10 Functions are used for computations.
11 Functions MUST return a value.
12 Function parameters are always IN.
13 UDF can be used in the SQL Statements anywhere in the WHERE/HAVING/SELECT section.
14 UDF's only can have input parameters.


Stored Procedures

1 Stored Procedure is pre compiled execution plan Functions are not precompiled.
2 Stored procedure returns always integer value by default zero.
3 Stored Procedure returns more than one value at a time.
4 We cannot call Stored Procedures in SQL Statements.
5 Function do not return the images, text. Stored Procedure returns all.
6 Procedures can be used for performing business logic.
7 Functions MUST return a value. Procedures need not be.
8 Function parameters are always IN, no OUT is possible Stored procedures can have input and output parameters.
9 Stored procedures cannot be used WHERE/HAVING/SELECT section .