Different Between Function and Procedure in Sql

1. Procedure can performs one or more tasks where as function performs a specific task.
2. Procedure may or may not return value where as function should return one value.
3. We can call functions in select statement where as procedure we cant.
4. We can call function within procedure but we can not call procedure within function.
5. A FUNCTION must be part of an executable statement, as it cannot be executed independently, whereas procedure represents an independent executable statement.


6. Function can be called from SQL statement where as procedure can't be called from the SQL statement.
7. Function are normally used for computation where as procedure are normally used for executing business logic.
8. Stored procedure supports deferred name resolution where as function wont support.
9. Stored procedure returns always integer value by default zero. where as function returns type could be scalar or table or table value.
10. Stored procedure is pre-compiled execution plan where as function are not.
11. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.
12. Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution.