Difference between Stored Procedure and User Defined Function in Sql Server

Answer:

1)Function must return a value.Stored procedure may or not return values.

2) Functions will allow only Select statement, it will not allow us to use DML statements.
Stored Procedures can have select statements as well as DML statements such as insert, update, delete etc

3)Functions will allow only input parameters, doesn’t support output parameters.But Stored Procedures can have both input and output parameters.

4) Transactions are not allowed within functions.We can use transactions within Stored procefures.

5) Stored procedures can’t be called from function.Stored Procedures can call functions.

6) UDF can be used in join clause as a result set.Procedures can’t be used in Join clause