Default IN

SQL Apprentice Question
I want to pass parameters to a sql query. For example:

WHERE ANS IN (" & MyString & ")


So if MyString = 'This', 'That', 'The Other'


It will populate the Where clause.


But I also want to be able to pass an empty string and have it return
all values for ANS.


Any ideas?


Celko Answers

>> I want to pass parameters to a sql query. For example: <<


This comes up all the time from Newbies who do not work with a compiled
language and have no idea what a parameter is. You will get a FAQ to
the standard kludges for this -- parsing routines without safety
checks, dynamic SQL that can crash, etc.

One answer is that T-SQL can handle up to 1024 parameters (REAL
parameters, not things parsed off a single string in procedural code)
and you will not need more than 100 in the real world. Suddenly, you
have all the power of the compiler to check data, to optimize, etc.


Another answer is to load a working table with a single column. The
bad news is that in the year 2006, SQL is missing basic parts of the
VALUES() clause that would make this very, very easy.