Difference between stored procedure and functions in SQL Server

1. Functions are compiled and executed at run time.
Stored procedures are stored in parsed and compiled format in the database.

2. Functions cannot affect the state of the database which means we cannot perform insert,delete,update and create operations on the database.
Stored Procedures can affect the state of the database by using insert,delete,update and create operations.

3 Functions are basically used to compute values. We passes some parameters to functions as input and then it performs some operations on the parameter and return output.
Stored procedures are basically used to process the task.

4.Function can not change server environment and our operating system environment.
Stored procedures can change server environment and our operating system environment.

5. Function always returns a Value.

Store Procedures may or may not return a Value.

6. Functions cannot call a store procedure.

Store Procedures can call a function.

7. Functions cannot handle Exceptions

Store procedures can handle Exceptions.

8. Store Procedures can have input,output parameters for it

Functions can have only input parameters.

9. Functions cannot handle transactions

Store procedures can handle transactions.

10. We can call a Function in a Select Statement.

We cannot call a Store Procedure in a Select Statement.