At 03:17 PM 9/12/2006, you wrote:

>Thanks for replying with a valuable suggestion. Until now I am not
>aware there is a 'starting with' syntax. I should read your book
>completely.
>
>Actually the integer value here, I mean the column number listed in
>the select statement. For example if the search has to by by vendor
>then i would use 1 for UPPER value, else if by department then I
>would use 2, for Category 3 and so on...

If by "column number" you are looking for a way to access the degree
number of the columns in the search clause then "no can do". The
search clause operates on the underlying tables, where degree is
irrelevant and, in the case of joins, impossible to determine.

When you use syntax like ORDER BY 2 or GROUP BY 1,2 the operation is
acting on the intermediate output, where degree is fixed.

In your SP, assuming the input argument SEARCHCHAR identifies the
position of the specified columns (and the identifier's name is
chosen for obscurity!!), you could use it if you coded some way to
decide the true identifier of the column that the number represents
*to you* and write separate SQL statements in branches. It wouldn't
be necessary to calculate the degree, though. You only need to
decide which column each number represents and code accordingly.

./heLen