We all know the relative general benefits of using database Stored Procedures.
Maintainability ◊ Testing ◊ Isolation of Business Rules ◊ Speed / Optimization ◊ Utilization of Set-based Processing ◊ Security
But in my opinion Gupta SQLBase stored procedures also stand head and shoulders above all other DBMS when it comes to traversing Result-Sets. This feature is so sadly under-marketed or utilised, and yet encapsulates all the reasons for using an SP in the first place and more.
All the SELECT SQL is pre-parsed and pre-compiled on the database when the SP is built, thus eliminating all that overhead from the front-end client, and help to keep it thin. The client can then request a result-set and then traverse it in any direction (or request a specific record) so easily.
This sounds like something that should be easy, but actually very few DBMS can maintain a result set and return any one of those selected records to the client via a Stored Procedure, with just a few lines of code.
It is so easy to move forwards or backwards through a result-set, returning any number of variables to the client, one record at a time (e.g. when clicking a ‘Get next Employee’ or ‘Get previous Employee’ push button).
In your Gupta TeamDeveloper code, build the Result-Set on the server using SqlRetrieve () then SqlExecute (), then navigate through it using SqlFetchNext () or SqlFetchPrevious () or SqlFetchRow ()
In your Gupta SQLBase Stored Procedure, handle requests from the client with ‘On Procedure Fetch’
In this case, each EMPLOYEE.SURNAME and FIRSTNAME will be returned to the client one at a time with each client request of SqlFetchNext () or SqlFetchPrevious () or SqlFetchRow ().