Function vs Stored procedure

Features	Procedures	Functions
Parameters	Supports in, out, in & out	Only supports in
Temp Object	Accessible – You can use the temp tables inside the procedure	Not supported
Create as Temp	Accepted – Create Proc #MyProc..	Not supported
Select Result	Supported	Not supported
Return	Return integer value	Returns any type of value
On DML Quires	Not allowed	You can embed the function on query
Calling SPs	Allowed	Not Allowed
Calling Another Functions	Allowed	Allowed
Insert/Update/Delete	Allowed	Not Allowed Or Only allowed against the table variables
Recursive Operation	Allowed	Allowed
Versioning (grouped)	Allowed	Not Allowed
Schema Binding	Not Allowed	Allowed
Creating Objects	Allowed	Not Allowed
EXEC	Allowed	Not Allowed
SP_EXECUTESQL Dynamic SQL	Allowed	Not Allowed
GETDATE() or other non-deterministic functions	Allowed	Not Allowed
SET OPTION	Not Allowed	Allowed
Setting Permission Grant/Deny	Yes	No for scalar functions. Yes for Table Values/Inline Functions

Functions
----------
1) can be used with Select statement
2) Not returning output parameter but returns Table variables
3) You can join UDF
4) Cannot be used to change server configuration
5) Cannot be used with XML FOR clause
6) Cannot have transaction within function

Stored Procedure
-----------------
1) have to use EXEC or EXECUTE
2) return output parameter
3) can create table but won't return Table Variables
4) you can not join SP
5) can be used to change server configuration
6) can be used with XML FOR Clause
7) can have transaction within SP

Function vs Stored procedure

1. Stored Procedure support deffered name resolution where as functions do not support deffered name resolution.
2. User Defined Function can be used in a select statement where as you cannot use a stored procedure in a select statement.
3. UDF's cannot return Image, Text where as a StoredProcedure can return any datatype.
4. In general User Defined Functions are used for computations where as Stored Procedures are used for performing business logic.
5. UDF should return a value where as Stored Procedure need not.
6. User Defined Functions accept lesser number of input parameters than Stored Procedures. UDF can have upto 1023 input parameters where as a Stored Procedure can have upto 21000 input parameters.
7. You cannot use non-deterministic built-in functions in UDF's. For example functions like GETDATE() etc can not be used in UDFs, but can be used in Stored Procedures.
8. Temporary Tables can not be used in a UDF where as a StoredProcedure can use Temporary Tables.
9. UDF can not Execute Dynamic SQL where as a Stored Procedure can execute Dynamic SQL.
10. User Defined Function does not support error handling where as Stored Procedure support error handling. RAISEERROR or @@ERROR are not allowed in UDFs.