Difference between Function and Stored Procedure (UDF vs SP)

  • Function can be used inside Select / Having / Where statement. Stored procedure can be used in Select / Having / Where section.
  • Stored procedure can call function but function can not call stored procedure
  • Function must return output but in stored procedure is it not mandatory to return output
  • Stored procedure can have output parameter but function will not have output parameter.
  • Function can not be used for Insert/Update, its select only. Stored procedure can be used for Insert/Update/Select.
  • Stored procedure can have exception handling code ( try-catch block ) . Exception handling code is not allowed in function.
  • Output Table valued functions can be used similar to table objects in SQL.
  • Stored procedures are complied code but function are compiled every time it execute. 
  • Functions stops execution on error. Stored procedure it just ignores the
    error and move to next statement.