Re: using a variable in an In(Clause)

[--CELKO--]

On Oct 18, 6:17 am, Paddy <paddymulla...@btopenworld.com> wrote:
> I have a value provided in the form of a string (varachar(max))
>
>> The value is of the type '1,2,3,4,5' <<

That is a VARCHAR(n) string and not a type of its own. SQL is a
compiled language, not interpreted like BASIC. It has one and only
one data structure -- the table. There are no vectors, arrays, lists,
etc.

>> It can be very long, but essentially it is list of comma separated values. <<

Define "very long"; if you mean 10,000 or so parameters, then this
ought to be put in a working table where you can scrub the data, use
an index, etc. If you mean 100 or fewer, then simply use a long
parameter list in a procedure call.

>> I want to use it in the where clause as part of the an 'IN' statement. <<

Newbies often want to do this, so there are a lot of kludges for it --
I know; I wrote one of the first of them years ago (single query with
a Sequence auxiliary table).

The problem is that the kludges do not handle the input list the same
way that the T-SQL compiler does. To do them right, you need to write
a parser in T-SQL and force the same error handling and messages. Or
you decide to either give up data integrity or to have two or more
different function behaviors in the system.

>> Dynamic SQL is not an option. <<

This is a good design decision. Dynamic SQL is a way of telling the
world that any future random user should be given control of the
system at run time. This is not usually a good thing