I'm at a client and they would like to be able to run their reports by typing in a few customer numbers, or have all of the customers automatically selected. There are over 30,000 customers.
I've tried two things, but I run into problems.
Declare @Cust varchar(max)
1. Where CustomerNumber like '%'+@Cust+'%' If they type in a customer or none at all, this works fine. If they type in two or more, then it breaks. It's a LIKE statement, not an IN statement.
2. Where CustomerNumber in (Select Value from Splitfuntion (@Cust,',')) This works fine if they type in one or more customers, but if they want all customers, then that's 30k splits for my IN statement. That kills the query.