Function Vs Stored Procedure

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