Re: input parameters

You can by constructing your select statement on-the-fly using native dynamic sql.

Just use an IF statement or two to create a string containing the statement you wish to execute. Then issue EXECUTE IMMEDIATE sqlstring;

This will, of course, work in a stored procedure but not in a command line SQL*PLUS session using ampersand variables.

Daniel A. Morgan

> I was wondering if I could perform the following SQL query but if someone
> leaves one or more of the criteria blank eliminate it from my search by --
> so if someone queries by state, leave the customer name and city out of the
> search or if some leaves the city blank query
> only by the name and the state given to the query -- here is the query
> SELECT A.NAME,B.CUSTOMER_ID, B.ADDRESS_ID,
> B.ADDRESS, B.PARSED_CITY, B.PARSED_STATE
> FROM CUSTOMER_INFO A,
> CUSTOMER_INFO_ADDRESS B
> WHERE A.NAME LIKE '&NAME'
> OR B.PARSED_CITY = '&CITY'
> OR B.PARSED_STATE ='&STATE'
> AND B.CUSTOMER_ID = A.CUSTOMER_ID;
Received on Tue Apr 03 2001 - 16:51:29 CDT