Latest News

Thursday, May 3, 2018

What is difference between PROCEDURE & FUNCTION ?

What is difference between a PROCEDURE & FUNCTION 

  • Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter.
  • Function can be called from SQL statements where as procedure can not be called from the sql statements 
  • Functions are normally used for computations where as procedures are normally used for executing business logic. 
  • You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.
  • Function returns 1 value only. Procedure can return multiple values (max 1024). 

  • Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named table 1 and table 2 etc..but actually not exists in database is allowed only in during creation but run-time throws error Function wont support deferred name resolution. 
  • Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values
  • Stored procedure is precompiled execution plan where as functions are not.
  •  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.
  • Google+
  • Pinterest

No comments

Post a Comment