Page 220 - Open Soource Technologies 304.indd

Unit 13: Stored Procedure

stored procedures as condition handlers also allows database administrators to track errors in Notes
the system with greater detail by using stored procedures to catch the errors and record some
audit information in the database or an external resource like a file.

13.2.3 Comparison with Dynamic SQL

• Overhead: Because stored procedure statements are stored directly in the database, they
may remove all or part of the compilation overhead that is typically required in situations
where software applications send inline (dynamic) SQL queries to a database. (However,
most database systems implement “statement caches” and other mechanisms to avoid
repetitive compilation of dynamic SQL statements.) In addition, while they avoid some
overhead, pre-compiled SQL statements add to the complexity of creating an optimal
execution plan because not all arguments of the SQL statement are supplied at compile
time. Depending on the specific database implementation and configuration, mixed
performance results will be seen from stored procedures versus generic queries or user
defined functions.
• Avoidance of Network Traffic: A major advantage with stored procedures is that they
can run directly within the database engine. In a production system, this typically means
that the procedures run entirely on a specialized database server, which has direct access
to the data being accessed. The benefit here is that network communication costs can
be avoided completely. This becomes particularly important for complex series of SQL
statements.
• Encapsulation of Business Logic: Stored procedures allow for business logic to be
embedded as an API in the database, which can simplify data management and reduce the
need to encode the logic else where in client programs. This may result in a lesser likelihood
of data becoming corrupted through the use of faulty client programs. The database system
can ensure data integrity and consistency with the help of stored procedures.

• Delegation of Access-Rights: In many systems, stored-procedures can be granted access
rights to the database which the users who will execute those procedures do not directly
have.

• Some Protection from SQL Injection Attacks: Stored procedures can be used to protect
against injection attacks. Stored procedure parameters will be treated as data even if an
attacker inserts SQL commands. Also, some DBMSs will check the parameter’s type.

13.2.4 Comparison with Functions
• A function is a subprogram written to perform certain computations and return a single
value.

• Functions must return a value (using the RETURN keyword), but for stored procedures
this is not compulsory.

• Stored procedures can use RETURN keyword but without any value being passed.

• Functions could be used in SELECT statements, provided they don’t do any data
manipulation. However, procedures cannot be included in SELECT statements.

LOVELY PROFESSIONAL UNIVERSITY 215