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 ()
![](/Data/Sites/2/media/teamdeveloper-storedproc-call.png)
In your Gupta SQLBase Stored Procedure, handle requests from the client with ‘On Procedure Fetch’
![](/Data/Sites/2/media/sqlbase-storedproc.jpg)
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 ().