Store Procedures Vs User Defined Functions is MS SQL Server

SQL Server user-defined functions and stored procedures offer similar functionality. Both allow us to create bundles of SQL statements that are stored on the server for future use. This offers a tremendous efficiency benefit, as you can save programming time.

    1. Reusing code from one program to another

    2. Hiding the SQL details, allowing database developers to worry about SQL and application developers to deal only in higher-level languages

    3. Centralize maintenance, allowing you to make business logic changes in a single place that automatically affect all dependent applications.

Difference between the functions and stored procedures:

Stored procedures are called independently, using the EXEC command, while Functions are called from within another SQL statement.

Stored procedure allow you to enhance application security by granting users and applications permission to use stored procedures, rather than permission to access the underlying tables.

Functions must always return a value (either a scalar value or a table). Stored procedures may return a scalar value, a table value or nothing at all. Stored Procedures returns more than one value at a time while Functions return only one value at a time.

Benefits of Stored Procedures:

    1. Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.

    2. Reduced client/server traffic. If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.

    3. 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, you'll find the development cycle takes less time.

    4. Enhanced security controls. You can grant users permission to execute a stored procedure independently of underlying table permissions.