Difference between stored procedure and function i.e user defined function in sql are listed below.
Function may return a value but in Stored Procedure it is optional( Procedure can return either zero or n values). Functions can have input parameters for it whereas Procedures can have both input and output parameters in it. Functions can be called from Procedure whereas Procedures cannot be called from Function.
Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
Benefits of Stored Procedures
- Stored procedures are precompiled execution. SQL Server compiles each Stored Procedure once and then re utilizes the execution plan. This results in tremendous performance boosts when Stored Procedures are called repeatedly.
- It reduced client/server traffic.If network bandwidth is a concern in your environment then you’ll be happy to learn that Stored Procedures can reduce long SQL queries to a single line that is transmitted over the wire.
- Efficient reuse of code and programming abstraction. Stored Procedures can be used by multiple users and client programs. If you utilize them in a planned manner then you’ll find the development cycle requires less time.
- It has enhanced security controls in it. You can grant users permission to execute a Stored Procedure independently of underlying table permissions.
Benefits of User Defined Functions
- They allow modular programming. You can create the function once, store it in the database, and call it any number of times in your program. User Defined Functions can be modified independently of the program source code.
- They allow faster execution. Similar to Stored Procedures, Transact-SQL User Defined Functions reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated executions. This means the user-defined function does not need to be reparsed and reoptimized with each use resulting in much faster execution times. CLR functions offer significant performance advantage over Transact-SQL functions for computational tasks, string manipulation, and business logic. Transact-SQL functions are better suited for data-access intensive logic.
- They can reduce network traffic. An operation that filters data based on some complex constraint that cannot be expressed in a single scalar expression can be expressed as a function. The function can then invoked in the WHERE clause to reduce the number or rows sent to the client.