What are the Differences Between Function and Procedure in SQL Server?

Hello everyone,

In this article, I will give information about what are the Differences Between Function and Procedure in SQL Server.

This question especially comes to mind of curious friends who are just learning SQL Server. This question may come up in job interviews. It is useful to know briefly, because in the programming world, it is more important to do something efficiently and with less resources than to do it.

1. The function must always return a value. (Number, text, table etc.) but Stored Procedure is optional, it may not have a return value.
2. You cannot use a try-catch construct in a function. You can use it in Stored Procedure.
3. Function can only be used with Select, it doesn’t update or delete, Stored Procedure can do all CRUD(CREATE/READ/UPDATE/DELETE) operations.
4. Function has only input parameters, but Stored Procedures can use both input and output parameters.
5. Function can be called by Stored Procedure but Stored Procedure cannot be called by Function.
6. Functions do not support Transaction constructs, but Stored Procedures do.
7. Only variable table values can be used in functions, Temporary tables cannot be used. You can use both in procedures.
8. Functions can be used anywhere in the WHERE/HAVING/SELECT section, while Stored Procedures cannot.
9. Functions can be called and used using Select and Stored Procedures using Exec or Execute.
10. We cannot use indexes in functions, they can be used in Stored Procedures.
11. Trigger is not used in Functions, it can be used in Stored Procedure.
12. While functions are not compiled once, Stored Procedures are compiled once and work efficiently.
13. It can be used in functions with Join structure, not in Stored Procedures.

I hope it was useful.

Good luck to everyone in business and life.