Explain the advantages and disadvantages of using stored procedures in transaction processing applications.

Question

What will be an ideal response?

Answer 1

1. Stored procedures can reduce the communication between server and client because only the arguments and the results need be communicated.
2. The statements within a stored procedure will have been prepared before execution of that procedure and will execute more e?ciently.
3. The checking of authorization privileges can be performed by the DBMS at the level of the stored procedure.
4. The application programmer need not knowthe details of the database schema, since all database accesses can be encapsulated within the procedure body.
5. Maintenance of the system is simpli?ed since only one copy of the procedure, stored on the server, need be maintained and updated.
6. The physical security of the code for the procedure is higher because it is stored on the server rather than with the application program.
7. A possible disadvantage is that since preparation is done in advance, the database might have changed and the selected query execution plan might be ine?cient. However, most systems allow preparation to be done over again at the discretion of the programmer or system administrator.