Stored Procedure vs. Function

Functions	Stored Procedure
Functions must return a value (scalar, inline table or multi statement table).	stored Procedure may or may not return a value
Functions can return a table	Stored Procedure can create a table but can't return table
Functions are called using select statements.	Stored Procedure can be called independently using exec keyword
Function can't be used to change server configuration(in terms of security-i.e. setting granular permissions of user rights)	Stored Procedure can be used to change server configuration(in terms of security-i.e. setting granular permissions of user rights)
XML and output parameters can't be passed to functions	XML and output parameters can be passed to stored Procedure
Transaction related statement can’t be handled in function.	Transaction related statement can be handled in stored procedure.

·        Stored Procedures can call a function or another stored Procedure similarly a function can call another function and a stored Procedure. The catch with function is that no user defined stored Procedure can be called. Only extended/system defined Procedures can be called