Don't be angry with me, Jeff. Perhaps I wrote too hastily, or maybe with the biasing of my particular environment. I do work on several DSS databases. My users are very (jealous? distruthful? pain of translators...) about required data. Data, moreover, are stable, so I assume that if they misspell something and do not get data, they'll be the first to realize. Thus my main concern is that their errors do not affect my procedure (as it would with duplicated values).

After this kind of apology, you'll see is not difficult to implement a mechanism to detect the wrong values, as you can see in the following (ad-hoc) code:

-- Let's say we have this clearly misspelled parameters ----

declare @param varchar(200)

set @param = 'WA,CA,Not_A-Valid_Param,TX,XX'

-- Here the reference table of valid values ----

declare @states table (

state char(2)

)

-- Only a excerpt ----

insert @states values ( 'WA' )

insert @states values ( 'CA' )

insert @states values ( 'TX' )

-- Let's add delimiters at the beginning and end of the string ----

set @param = ',' + @param + ','

-- Now the piece of code to get the

values ----

-- I added the orderby column to make the algorithm

-- sensible to the input order (if it matters) ----

declare @param_table table (

state char(2),

orderby int

)

insert @param_table

select state, charindex( ',' + state + ',', @param ) as orderby

from @states

-- Ad-hoc method to get incorrect values: ----

-- Suppress the valid ones from the input string ----

select @param = replace( @param, state, '' )

from @param_table

order by orderby

-- For the sake of comparison, I create what would be the valid string ----

declare @s-2 varchar(800)

set @s-2 = ''

select @s-2 = @s-2 + ',' + state

from @param_table

order by orderby

-- Get rid of repeated commas ----

while ( charindex( ',,', @param ) > 0 )

set @param = replace( @param, ',,', ',' )

-- Good parameters to the right of the Father, the bad to the left ---

-- (Excuse me for the extra commas - quick and lazy programming after all) ----

select @s-2 As GOOD_PARAM, @param AS BAD_PARAM

Finally, I disagree with you about right or wrong things. In my opinion, circumstances determine how good a solution is; what fits in some cases, does not in others.

Nevertheless, your opinion is valuable to me, although a little rough for my taste. Sorry if I offended you, anyway.