From the viewpoint of an ODBC-compliant data source, a stored procedure is a predefined procedure or set of statements or program that is defined for a specific data source according to that data source's rules. By executing the stored procedure, you execute these statements or programs without having to enter their individual statements or code at each execution.
Note: This chapter uses the phrase RDBMS stored procedure and stored procedure interchangeably.
Enhancements to stored procedure techniques also allow you to extend your code in new ways and effectively employ more ABL features and functionality. For example, you can retrieve data from a foreign data source through a stored procedure and load this result set into a temp-table. Also, this technique is especially useful if you elect to further manage and manipulate your result set using ProDataSet capabilities.
Stored procedures can allow you to improve your dataserver's performance because they utilize the foreign data source's native capabilities. Stored procedures can be an efficient, productive approach to obtain data from a foreign data source or execute native business rules and procedures. In general, using stored procedures can help reduce network traffic, promote better performance, and improve response time because the server-side processing of the stored procedure can be accomplished without sustained communication with the client; once this processing is completed, the server returns the data results to the requesting client.
The first task to effectively employ any stored procedure technique or approach is to determine what information you want to receive or process you want to run. Once you know your data requirements, you can proceed to define the stored procedure details.
Note: A complete discussion of creating and using native stored procedures is beyond the scope of this chapter. For this type of information, see your Microsoft SQL Server documentation.
