Stored Procedure vs Function

Stored Procedure	Function
Return type is not must, Can return zero, single or multiple values or table(s)	Return type is must, and it can return one value which is mandatory. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
SP can have input/output parameters	Support only input parameters
Allow Select as well as DML statements.	Allow Select but not DML statements. Note: In case of multi-table valued functions it can contain DML statements affecting Table Variables.
Stored procedure can execute function.	Function cannot execute stored procedure.
Cannot be the part of Select query as a column.	Can be the part of select query as a column
Stored Procedures cannot be embedded in the SQL statements like WHERE/HAVING/SELECT	Functions can embedded in the SQL statements like WHERE/HAVING/SELECT
We can use exception handling using Try....Catch block in SP.	We can’t use Try....Catch block in UDF
We can use transactions in SP	We can’t user transaction in UDF
Can have up-to 21000 input parameters	UDF can have up-to 1023 input parameters

Why we can't execute stored procedure inside a function?

Answer:

1. Stored Procedure may contain DML statements.


2. Function can't contain DML statements.
   So executing Function inside stored procedure will never break rule 1.
   But executing stored procedure inside function may break rule no 2.

Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e. no INSERT or UPDATE statements allowed). It returns error like “Invalid use of a side-effecting operator 'INSERT' within a function.” So ultimately strict rule is made by Sql team: we can't execute stored procedures inside function.