Difference between Stored Procedure and User Defined Function in Sql Server

Sl. No.	User Defined function	Stored Procedure
1	Function must return a value.	Stored procedure may or not return values.
2	Will allow only Select statement, it will not allow us to use DML statements.	Can have select statements as well as DML statements such as insert, update, delete etc
3	It will allow only input parameters, doesn’t support output parameters.	It can have both input and output parameters.
4	It will not allow us to use try-catch blocks.	For exception handling we can use try catch blocks.
5	Transactions are not allowed within functions.	Can use transactions within Stored procefures.
6	We can use only table variables, it will not allow using temporary tables.	Can use both table variables aswell as temporary table in it.
7	Stored procedures can’t be called from function.	Stored Procedures can call functions.
8	Functions can be called from select statement.	Procedures can’t be called from Select/Where/Having etc statements. Execute/Exec statement can be used to call/execute stored procedure.
9	UDF can be used in join clause as a result set.	Procedures can’t be used in Join clause

REF: https://sqlhints.com/2012/06/16/difference-between-stored-procedure-and-user-defined-function-in-sql-server/

	Stored Procedure	Functions
Compilation	Stored in database in compiled format. Note: Compiled indicates, Execution plan will be made by sql at the time it created and stored in DB.	Will compiled at run time
Return type	It can directly return only integers Return type is not must	It can return any scalar or table Return type is must
Multiple return values	It can also return more than one values (of any data type) indirectly with the help of out parameters	It won't support out parameters
DML Statements	Can have DML statements.	Cannot have DML statements. Note: In case of multi-table valued functions it can contain DML statements affecting Table Variables.
Execution	Stored procedure can execute function. Cannot be the part of Select query as a column. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT	Function cannot execute stored procedure. Can be the part of select query as a column. Functions be used in the SQL statements anywhere in the WHERE/HAVING/SELECT
Exception handling	Can have Try....Catch	Cannot have Try....Catch

Sometimes we face a question, 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.

 
So ultimately strict rule is made why Sql team, we can't execute stored procedures inside function.

 Ref: https://www.dotnetinterviewquestions.in/article_what-is-the-difference-between-stored-procedures-and-function-sql-server-interview-question-with-answers_158.html