About Stored Procedures - Firebird

Stored procedures can be used in applications in a variety of ways.

  • Selectable procedures are used in place of a table or view in a SELECT statement.
  • Executable procedures are used with an EXECUTE PROCEDURE statement to perform a single operation or start a set of operations on the server side.
  • A stored procedure can be invoked by another stored procedure or by a trigger. It can call itself recursively.

All stored procedures are defined with the complex DDL statement CREATE PROCEDURE. Executable and selectable stored procedure declarations follow the same syntax rules. Optional language elements distinguish a selectable one from an executable one. One procedure can be nested within another, each performing a part of an atomic sequence of work that will be committed by the client application as a whole or rolled back as a whole.

Benefits of Using Stored Procedures

The benefits of using procedural code modules that run inside the database include

  • Modular design: All applications that access the same database share stored procedures, thus centralizing business rules, reusing code, and reducing the size of the applications.
  • Streamlined maintenance: When a procedure is modified, changes propagate automatically to all applications without the need for further recompiling on the application side, unless changes affect input or output argument sets.
  • Improved performance: Execution of complex processing is delegated to the server, reducing network traffic and the overhead of operating on external sets.
  • Architectural economy: Client applications can focus on capturing user input and managing interactive tasks while delegating complex data refinement and dependency management to a dedicated data management engine.
  • Extra functionality: Tricky accessing and massaging of data that cannot be achieved with regular SQL usually can be managed with one or a suite of stored procedures.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics