Re: Stored Procedures

There is a neat trick you can do using ORs. the only problem with this approach is that Informix might not be able use an index on the columns you are searching on. SELECT * FROM customer WHRE (custumer_name = v_cust_name OR customer_nam = customer_name) AND (customer_no = v_cust_no OR customer_nor = customer_no) AND (customer_state = v_cust_state OR customer_state = customer_state) Where v_* are the variables. ------------------------------------------------------------------------------------------------------------------------------ Rafall Czerniawski Dataspace Consulting Pty Ltd Sydney, Australia Email: rafal@magna.con.au jgordon@us.DHL.COM (Jim Gordon) wrote: >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!