Differences between Stored Procedures and Functions

Store Procedure:

Stored Procedure is a group of sql statements that has been created once and stored in server database. It’s pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called.

Functions:

Function is not pre-compiled object it will execute every time whenever it was called.

Built in FUncitons: These are defined by the SQL server itself and we will not be able to do any modifications to these functions.

User Defined Funcitons: These will be created by the users using CREATE FUNCTION statement. We can have Zero or more input parameters to the funciton and every function should return a value.

Differences between Stored Procedure and User Defined Function in SQL Server

Sr.No.	User Defined Function	Stored Procedure
1	Function must return a value.	Stored Procedure may or may not return values.
2	It Will allow only Select statements	It can have select statements as well as DML statements such as insert, update, delete etc.
3	It will allow only input 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 temporary table in it.
7	Stored Procedures can't be called from a function.	Stored Procedures can call functions.
8	Functions can be called from a select statement.	Procedures can't be called from Select/Where/Having and so on statements. Execute/Exec statement can be used to call/execute Stored Procedure.
9	A UDF can be used in join clause as a result set.	Procedures can't be used in Join clause