Both Function and Stored Procedure (SP) are pre compiled SQL statements but there some basic differences between them:
- Function must return a value whereas SP may or may not return a value.
- A function can be used in a Join whereas SP can't be used in a Join.
- Functions can return a table whereas SP can create a table but can't return table.
- XML and output parameters can't be passed to functions whereas it can be with SP.
- Transaction related statement can be handled in SP whereas it can't be in function.
- We can have extended SP but not extended functions.
- SP can call a function or another stored proc similarly a function can call another function and a stored proc. The catch with function is that no user defined SP can be called. Only extended/system defined procedures can be called.
- SP can be called independently using exec keyword whereas function are called using select statements.
- SP can be used to change server configuration(in terms of security-i.e. setting granular permissions of user rights) whereas function can't be used for this
