Difference between Stored procedure vs User Functions in Sql server
• Procedure may return none or more values. Function must always return one value either a scalar value or a table.
• A user-defined function takes zero or more input parameters and returns either a scalar value or a table.
• Procedure have input,output parameters.Functions have only input parameters.
• Stored procedures are called independently by EXEC command whereas Functions are called from within SQL statement.
• Functions can be called from procedure. Procedures cannot be called from function.
• Exception can be handled in Procedure by try-catch block but try-catch block cannot be used in a function.(error-handling)
• Transaction management possible in procedure but not in function.