hope (and actually think :) ) that some one already had similar problem and solved it ..
the basic issue in short terms .. as the use of a 'wordsearch' feature and the related data will grow rapidly in near future we (in that case means me) need to change to using fulltext search ..
so its all setup now .. but i'm unable to make the querysyntax work (simplified version below)
declare @searchTerm varchar(256)
set @searchTerm = 'test'
declare @searchValues table (code varchar(900), searchterm varchar(256))
-- this is just for testing, usually the table is prefilled with different codes and values
if(@searchTerm is not null)
insert into @searchValues
select code, @searchTerm from dbo.base_question where question_type_id = 'text'
select distinct(a.item_id)
from dbo.answer_text as a with (nolock)
join dbo.base_question as bqt with (nolock) on bqt.id = a.base_question_id
join @searchValues as st on bqt.code = st.code
where a.value is not null
and not LTRIM(RTRIM(a.value)) = ''
and CONTAINS(a.value, st.searchterm)
the where clause is part of a much larger one .. just guessing i would think the 'CONTAINS'-clause needs a kind of 'static' searchterm input and can't handle a row-dependent value ..
couldn't find anything in the msdn nor here ..
one of the major problems is that the 'base_questions' are not fixed and may change and the search-conditions (which question to search for what term) are completely dynamic .. so no switch-case or something like that .. and i think dynamic sql is also not really an option (for different reasons but that would go far beyond the scope of this question :) .. )
however all searches to this database are done via stored procedures with scalar and table-valued parameters (like @searchValues ) .. i would like to keep it that way if somehow possible
so any hints, tips & suggestions are appreciated
and FYI and completeness short version of the simplified db-structure:
answer (id bigint (PK), item_id uniqueidentifier (FK), base_question_id uniqueidentifier (FK), value nvarchar(max))
base_question (id uniqueidentifier (PK), code varchar(900), question_type_id (FK), .. descriptive content ..)
item (id uniqueidentifier (PK), .. descriptive content ..)
.. even open to changing the db-structure to make the searches somewhat performant and working again ;) .. (right now we have around 120k item_ids and around 2.5 mio answers for them but that will increase easily by 10 times and more)
