Difference between Stored Procedure and User Defined Function in SQLServer

Sl. No.	User-Defined function	Stored Procedure
1	The function must return a value.	The stored procedure may or not return values.
2	Will allow only Select statements, 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 and 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 procedures.
6	We can use only table variables, it will not allow using temporary tables.	Can use both table variables as well as a temporary table in it.
7	Stored procedures can’t be called from function.	Stored Procedures can call functions.
8	Functions can be called from a select statement.	Procedures can’t be called from Select/Where/Having etc statements. Execute/Exec the statement can be used to call/execute a stored procedure.
9	UDF can be used in the join clause as a result set.	Procedures can’t be used in the Join clause

Architect