What are the differences between Stored Procedures and Functions within Sql Server?

Stored Procedure are objects that are precompiled by SQL and whenever calls are made from your "precompiled" code. But the Function function is compiled and executed every time it is called. Let's look at some differences between these two ways of handling data within our database.

The basic difference

• Function should return a value, but in stored return is optional.
• Functions can only have input parameters. Stored have input / output parameters.
• Functions can be called from within storeds, since storeds do not can be called from functions.

Advanced Differences

• Procedures can not be used in a SELECT statement while that the function can be embedded in a SELECT statement.

• Procedures can not be called and / or used in the SQL statement anywhere in WHERE / HAVING / SELECT, while the function can be.

The most important feature of storeds procedures in relation to functions is the retention and reuse of the execution plan, whereas in the case of the function that will be compiled each time it is executed. >

• Functions that return tables can be treated like one another set data. This means that we can use them in associations (JOINS) with other tables.

• Exceptions can be handled by try-catch block in a storeds while try-catch block can not be used in a function.

• We can use Transaction Management in a stored, not in a stored function.

I hope I have helped.

Source: Simple Code