>>>>Hi,
>>>>
>>>>I have a place in the program where the SQL Select (that selects records for a Cursor Adapter) is built dynamically as follows:
>>>>
>>>>

>>>>select * from MyTable where OrderNo in (Number1, Number2, .... )
>>>>

>>>>
>>>>The part in parenthesis is built dynamically and the user can select pretty much any number of order numbers to include. So if the entire SQL Select becomes too long, it bombs. Any suggestions on how I could simplify this but still allow user to select any number of "orders"?
>>>>
>>>>TIA
>>>
>>>The syntax for doing so would be a little adventurous but ... you can create a temporary table in SQL Server with the list of numbers, and have your main query use that as an IN SELECT target. That should be more optimizable than what you have.
>>>
>>>Hank
>>
>>Interesting approach. But I am not sure it would work for my case. I will have to see.
>>Thank you.
>
>Hank's idea is the "correct/proper" approach but it requires server-side changes in SQL Server. Another, less optimal variation would be:
>
>- Have a local view open that contains *all* orders
>- Build a *local* cursor with the order numbers selected by the user
>
>Then your SQL could be something like
>

>SELECT * FROM {View} WHERE OrderNumber IN ( SELECT * FROM {LocalOrderNumbersCursor} )
>

>Since this pulls all Order information over the wire (for the View) it's not efficient/scalable for large numbers of Orders or slow connections. But it has the advantage that you can do it all client-side.
>
>In some cases multi-select lists are powered by cursors, where when an item is selected a cursor column such as lSelected gets set to .T.. In that case this approach would be even easier, you wouldn't even have to build a cursor to hold the selected values, you could do something like
>

>SELECT * FROM {View} WHERE OrderNumber IN ( SELECT OrderNumber FROM {SelectionsCursor} WHERE lSelected )
>

First, thank you for your detailed suggestion. But I don't think this will work for me. In my case, I have to pass this "SQL Select" string to the BIZ object which creates a Cursor Adapter. To change this, would require changing the BIZ object. I am not sure I want to do it for this particular case.

I think I need to figure a way to check the length of the SQL Select, before sending it to the BIZ object, and if it is longer than allowed (by the what, what is the max length of the SQL Select string I can have?) than I will inform user to break his/her requirement into a smaller.