Monday, 16 May 2016

Difference between Function and Procedure



  1. Function is mainly used in the case where it must return a value. Whereas a procedure may or may not return a value or may return more than one value using the OUT parameter. 
  2. Function can be called from SQL statements whereas procedure cannot be called from the SQL statements 
  1. Functions are normally used for computations whereas procedures are normally used for executing business logic. 
  1. You can have DML (insert, update, delete) statements in a function. But, you cannot call such a function in a SQL query.
  1. Function returns 1 value only. Procedure can return multiple values (max 1024). 
  1. 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 won't support deferred name resolution. 

  1. Stored procedure returns always integer value by default zero. whereas function return type could be scalar or table or table values
  2. Stored procedure is precompiled execution plan whereas functions are not.
  3. A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller. 
  4. We can't have any DDL,DML and TLC command inside a function, if that function is called from a query. But if the function is not called from query then we can have all transactional statement(DDL,DML and TLC ) inside a function.

No comments:

Post a Comment