The difference between stored procedures and functions in SQL

There is essentially no difference. It's just that the function has the restriction that it can only return one variable. The stored procedure can return multiple. Functions can be embedded in SQL and can be called in select, but stored procedures cannot. The essence of execution is the same.
There are many restrictions on functions. For example, temporary tables cannot be used, only table variables can be used. There are also some functions that are not available and so on. There are relatively few restrictions on stored procedures
1. Generally speaking, the functions implemented by stored procedures are more complex, while the functions implemented by functions are more targeted.
2. For stored procedures, parameters can be returned, while functions can only return values ​​or table objects.
3. Stored procedures are generally executed as an independent part (EXEC execution), while functions can be called as part of a query statement (SELECT call). Since the function can return a table object, it can be used in the query statement. Located after the FROM keyword.
4. When stored procedures and functions are executed, SQL Manager will go to the procedure cache to get the corresponding query statement. If there is no corresponding query statement in the procedure cache, SQL Manager will compile the stored procedures and functions.
The execution plan is saved in the procedure cache. After it is compiled, the execution plan in the procedure cache is executed. Afterwards, SQL SERVER will consider whether to save this plan in the cache based on the actual situation of each execution plan. One of the criteria for judging is the frequency with which this execution plan may be used; the second is the cost of generating this plan, which is the time it takes to compile. The plan saved in the cache does not need to be compiled the next time it is executed.