Re: Stored Procedures

On Oct 17, 2:04pm, Sanjay Kumar wrote: > Subject: Stored Procedures > > I want to create a stored procedure in informix SPL where i dont know > the WHERE clause of the select statement. As far as I could gather > from the manuals ; I can only select if the where clause is known. > > My requirement is as follows :- > > I want to write a stored procedure to get all the customers based on > the user input . He/she can search it based on Customer # ( char (6), > Customer name ( char (40 ) or Customer State ( char(2) ). > > Since I dont know what will be entered by the user ; hence I want to > implement QBE in the stored procedure ? Is it possible. > > Thanx > ------------ > Sanjay Kumar Email: skumar@raileurope.com True QBE, as per construct in 4GL, is not possible in SP's and would be against the SP philosphy as they are intended for pre-compilation of SQL. However, we have achieved the same effect by accepting the parameters into a SP which works out which fields have been populated for the query. It then calls a SP, which is one of the full set of SP's that have been written, containing the query statement that matches the parameters specified. In other words we create all the possible query statements in advance, the sets can get pretty large! The query result is then passed back through the top level SP. This has the advantage of QBE with pre-compilation but is expensive to develop. One way to reduce the number of queries, and therefore SP's, in the full set is to use queries that do not exclude all rows not required by the users parameters. Further code processing in the SP is used to remove the unwanted rows from the returned set. For some specific requirements this technique can have faster response than QBE as each query can be specifically optimised for the required return set. Cheers - Jim -- ----------------------------------------------------------------------------- Jim Gordon DHL Airways Inc. jgordon@us.dhl.com ----------------------------------------------------------------------------- My opinions are my own. They may vary with time but they remain mine!