Difference between Store Procedure and Function(UDF)

  • Function must return value but SP not.
  • Function can be used in the sql statements anywhere in the WHERE/HAVING/SELECT section where as SP cannot be.
  • Function has not output parameter but SP has
  • If there is an error in Function its stops executing. But in SP’s it just ignores the error and moves to the next statement
  • UDF cannot make permanent changes to server environments while SP’s can change some of the server environment.
  • EXEC command can't be used inside a Function where it can be used inside an sproc.
  • Functions are normally used for computations where as procedures are normally used for executing business logic.
  • Stored procedure returns always integer value by default zero. Whereas function returns type could be scalar or table or table values


Post a Comment