Difference between Stored Procedure and Function in SQL Server

1. Stored Procedures are compiled when it runs first time in Database but, Function is compiled and executed every time when it is called.

2. Stored Procedures can have input/output parameters whereas Functions can have only input parameters.

3. Stored Procedures, we can declare both DDL/DML operation But, In Function we can't.

4. We can execute function inside Stored procedure whereas we can't execute  Stored procedure inside function.

5. We can declare Error handling (Try. Catch) inside Stored procedure But, for Function we can't.

6. Stored Procedures cannot be embedded in a SELECT statement whereas Function Can.

7. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can.