User Defined Functions and Stored Procedures

1. UDF are simpler to invoke than Stored Procedures from inside another SQL statement. They are Complex to Invoke.
2. SQL functions that return non-deterministic values are not allowed to be called from inside User Defined Functions. GETDATE is an example of a non-deterministic function. Every time the function is called, a different value is returned. Therefore, GETDATE cannot be called from inside a UDF you create, but could not be used inside the function itself. Non-deterministic values are not allowed to be called from inside Stored Procedure.
3. The User Defined Function must be prefaced with the owner name, DBO in this case. Not mandatory.
4. When call function then the parameter must transmit full.
   Ex:
   CREATE FUNCTION ftest(@val01 varchar(10),@val02 varchar(10) )
   CREATE PROCEDURE pTest(
   @val01 varchar(10) = ‘10',
   @val02 varchar(10) = NULL
   )
   Call:
   dbo.ftest(@val01,@val02) —-> OK
   dbo.ftest(@val01) —-> NOT OK
   When call store procedure then the parameter allow empty.
   CREATE PROCEDURE pTest(
   @val01 varchar(10) = ‘10',
   @val02 varchar(10) = NULL
   )
   Call:
   dbo.ftest(@val01,@val02) —-> OK
   dbo.ftest(@val01) —-> NOT OK
   pTest(@val01,@val02) —-> OK
   pTest(@val01) —-> OK
5. A UDF is always used to return a value or a table object. You can also get values back from a stored procedure by the return code (integer only) or an output parameter.
6. Function return type could be scalar or table or table values (SQL Server). Stored procedure returns always integer value by default zero.
7. Stored Procedure is pre compiled execution plan Functions are not precompiled.
8. Function returns only one value at a time. Stored Procedure returns more than one value at a time.
9. We can call the functions in SQL statements (select max(sal) from emp). We cannot call Stored Procedures in SQL Statements.
10. Functions do not return the images, text. Stored Procedure returns all.
11. Functions are used for computations. Procedures can be used for performing business logic.
12. Functions MUST return a value. Procedures need not be.
13. Function parameters are always IN, no OUT is possible Stored procedures can have input and output parameters, while user defined functions only can have input parameters. Output parameters must be returned as return values.
14. UDF can be used in the SQL Statements anywhere in the WHERE/HAVING/SELECT section Stored procedures cannot be.
15. If you have an operation such as a query with a FROM clause that requires a row set be drawn from a table or set of tables, then a function will be your appropriate choice. However, when you want to use that same row set in your application the better choice would be a stored procedure.