Differences between Functions and Stored Procedures

Stored Procedre:

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. 

Function:

Function is not pre-compiled object it will execute every time whenever it was called.

Difference between Stored Procedure and Function

1) Stored Procedure can return zero or n values whereas function can return one value which is mandatory Functions must always return a value (either a scalar value or a table). Stored procedures may return a scalar value, a table value or nothing at all.

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

3) Stored 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) Stored Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.

8) Stored procedures are called independently, using the EXEC command, while functions are called from within another SQL statement. 

1. Unlike Stored Procedure, Function returns only single value.
2. Unlike Stored Procedure, Function accepts only input parameters.
3. Unlike Stored Procedure, Function is not used to Insert, Update, Delete data in database table(s).
4. Like Stored Procedure, Function can be nested up to 32 level.
5. User Defined Function can have upto 1023 input parameters while a Stored Procedure can have upto 21000 input parameters.
6. User Defined Function can't returns XML Data Type.
7. User Defined Function doesn't support Exception handling.
8. User Defined Function can call only Extended Stored Procedure.
9. User Defined Function doesn't support set options like set ROWCOUNT etc.
10.  

    Store Procedure	Function
    S.P need not be return a value	Functions MUST return a value
    S.P can be called independently using exec keyword whereas	Function is called using SELECT statements.
    S.P can be used for performing business logic	Functions are used for computations
    Can be used EXEC inside an S.P	EXEC command can't be used inside a Function
    S.P takes IN and OUT parameters.	Function parameters are always IN, no OUT is possible
    Transaction related statement can be handled in S.P	Can't be handled in function.
    XML parameters passed in S.P	Can’t pass XML Parameters
    S.P can affect the state of the database by using insert, delete, update and create operations.	Functions cannot affect the state of the database which means we cannot perform insert, delete, update
    Parsed and Compiled during design time itself	Compiled and executed during run time