I have a query module where the user can select a variety of conditions (reason codes, text searches, amount and date range, etc.) I build a WHERE clause and pass it through to SQL Server.
I also allow a user to select a list of products and accounts. They may be picking from a long list. They might pick 2, they might pick 20, they might pick 200...and I need to get them into the mix of the query.
One way (which I've tried before, and I know works) is to build a #TEMPTABLE in SQL Server (scoped to that user's connection) with a list of account and product keys chosen by the user. Then my query can do a join against the #TEMPTABLES created.
Another way is to just build a (long) SQL statement, using INLIST (or IN...I forget the exact syntax). However, I'm concerned about the length of the SQL statement that I might be building. Assuming my account key is 4 bytes long, if a user selects 200 accounts, I'd have a SELECT SQL string that's almost 1,000 bytes just for that.
Which practice is better?
Thanks,
Kevin