Stored procedure


What are the advantages of stored procedure?

  1. Atomicity : SQL commands in a stored procedure are within transaction scope, which means that either all of the SQL statements in a stored procedure will execute, or none will. This is known as atomicity.

  2. Speed : Unlike standard SQL statements, stored procedures are compiled and optimized by the database server. This optimization involves using information about the structure of a particular database that’s required at execution time by the stored procedure. This process of storing execution information (the execution plan) is a time saver, especially if the stored procedure is called many times.

  3. Flow Control : A stored procedure can take advantage of control flow statements such as IF…ELSE, and FOR and WHILE loops, that are not typically available within a basic SELECT statement.

  4. Security : Stored procedures can also act as an additional security layer. For example, we could allow access to a stored procedure that generates an average salary for a company, while never allowing its users to see the salary information directly.

  5. Reduced Network Traffic : A properly designed application that processes large amounts of data using stored procedures returns only the data that is needed by the client. This reduces the amount of data transmitted across the network.

  6. Modularization :Modularization is the process of writing reusable code units.