Re: Report Writers, Stored Procedures, etc

> My idea was that if Stored Procedures could be used as part of the > Create view statement in place of the select statement and the Stored> Procedures had some way of knowing whether an Insert, Delete, Update > or Select function had been used. Using this method much of our Data > Access Module could be replaced by these views which could be used as > if the underlying database looked like the view but in reality the > Stored Procedure was handling the work on as many tables as is > necessary for the business transaction. > > etc > > Cheers - Jim After considering this idea further I have realised that I am probably trying to ask for more than is reasonable. I still think the basic idea is sound but I had forgotten the necessity for dealing with select/update/delete "where" clauses that an application might use against the view. Also I was, to an extent, confusing the use of a Stored Procedure in a View and also within a cursor. I still think that being able to but Stored Procedures under views could be helpful but this is bound to have a performance hit as the Stored Procedure would have to provide all possible rows to the external Select/Update/Delete and have the external process select the correct rows based on the appropriate "where" clause. This would mean that no filter or index could be used to speed the process unless very complex code was written to parse the "where" clause in the Stored Procedure (Yuck). So having considered this further and before anybody replies I would like to change my suggestion to extend the use of Stored Procedures in Cursors. Currently it is possible to declare a SP as a cursor, Open it and fetch results from the SP. I would like to see this extended so that you can declare the SP as a cursor for update. Then you could open it and fetch from it but also be able to pass data back into it. Now this could be done using the PUT command familiar from Insert cursors or UPDATE WHERE CURRENT OF or something new. It does not matter as long as it is possible to change the values originally passed to the SP in the OPEN statement. One of these values can be an action identifier used by the SP to know what action to take with the new data. This facility would be very useful where you have an array of data to process and do not want the cost of re-opening or re-executing the SP each time. This can be a major factor especially if the SP does some setup work each time. Also it would allow the SP to handle arrays and totalling for the transaction instead of needing to write that into the cursor processing code. I hope that this clarifies my ideas and steps neatly around any rude comments darting in my direction. Cheers - Jim -------------------------------------------------------------------- Name: Jim Gordon Internet: jgordon@ssf-sys.DHL.COM Company: DHL Systems Inc Phone: (415) 358-5911 (Work) Address: 1700 S. Amphlett Blvd. (415) 882-9728 (Home) San Mateo, CA 94402 Fax: (415) 571-6429 --------------------------------------------------------------------