Differences between Stored Procedure and User Defined Function in SQL Server

Stored Procedure is a group of SQL statements that has been created once and stored in server database. It’s pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. Stored procedures will accept input parameters so that single stored procedure can be used over network by multiple clients using different input data. Stored procedures will reduce network traffic and increase the performance.

Benefits of Stored Procedures
- Precompiled execution
- Reduced client/server traffic
- Efficient reuse of code and programming abstraction
- Enhanced security controls

Function is compiled and executed every time when it is called. SQL Server User Defined Functions are routines that accept parameters, perform an action such as a complex calculation, and returns the result of that action as a value. The return value can either be a single scalar value or a result set.

Benefits of User Defined Functions
- They allow modular programming
- They allow faster execution
- They can reduce network traffic

Differences :

1) Procedure can return zero or n values whereas function can return one value which is mandatory.

2) Procedures can have input, output parameters for it whereas functions can have only input parameters.

3) Procedure allows select as well as DML(INSERT/UPDATE/DELETE) statements in it whereas function allows only select statement in it.

4) Functions can be called from procedure whereas procedures cannot be called from function.

5) Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.

6) We can go for transaction management in procedure whereas we can't go in function.

7) Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.

8) Stored Procedure can have upto 2100 input parameters. User Defined Function can have upto 1023 input parameters.

9) We can’t join Stored Procedure, But We can join functions.

Sr.No.	Stored Procedure	Functions
1.	Procedure can return zero or n values.	Function can return one value which is mandatory.
2.	Procedures can have input, output parameters for it.	Functions can have only input parameters.
3.	Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statements.	Function allows only select statement.
4.	Procedures cannot be called from function.	Functions can be called from procedure
5.	Exception can be handled by try-catch block in a procedure	Try-catch block cannot be used in a function.
6.	We can go for transaction management in procedure.	We cann't go for transaction management in functions.
7.	Procedures cannot be utilized in a select statement.	Function can be embedded in a select statement.
8.	Stored Procedure can have upto 2100 input parameters.	User Defined Function can have upto 1023 input parameters.
9.	We can't join Stored Procedure.	We can join functions.

Why can’t execute stored procedure inside a function?

=> Stored Procedure may contain various Data Manipulation Commands. But function can’t, so executing function inside the procedure break this rule.