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.