Examining the Two Data Access Techniques: Stored Procedure and Dynamic SQL

Abstract

A stored procedure is a group of SQL statements that form a logical unit and perform a particular task, and they are used to encapsulate a set of operations or queries to execute on a database server. For example, operations on an employee database (hire, fire, promote, lookup) could be coded as stored procedures executed by application code. They perform mid-way processing on the database server and so exclude transmission of excess data across the network. Dynamic SQL is a programming technique that enables you to build SQL statements dynamically at runtime. You can create more general purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation. This paper states the comparison and investigation of two relational database access mechanisms i.e. Stored Procedures and Dynamic SQL. Stored procedures when implemented resulted in speedy database operations in comparison to dynamic SQL over the Internet.