Jason writes "My goal is to pass a comma delimited list of words into a stored procedure, or simply a phrase with spaces in it, which I'll convert into an array. Then I'd like to create dynamic SQL within the stored procedure which would result in the following SQL statements (An example phrase is supplied)'how are you' or 'how','are','you' is passed in to create:SELECT phrase_id, '''' + Replace(Replace(p.phrase,',',''), ' ', ''',''') + '''' AS textINTO #reply_tempFROM phrase AS pWHERE phrase LIKE '%how%'OR phrase LIKE '%are%'OR phrase LIKE '%you%'
SELECT reply_id,CASE WHEN CHARINDEX('''how''',text) > 0 THEN 1 ELSE 0 END + CASE WHEN CHARINDEX('''are''',text) > 0 THEN 1 ELSE 0 END + CASE WHEN CHARINDEX('''you''',text) > 0 THEN 1 ELSE 0 ENDAS word_countFROM #reply_temp AS t, phrase AS p, phrases AS rWHERE t.phrase_id = p.phrase_id AND p.phrase_id = r.phrase_idORDER BY word_count DESC, phrase_count DESC, p.phrase_id DESC, reply_count DESC, reply_id DESCJust as a briefing, this stored procedure counts how many words in the phrase in the database match the supplied phrase. There may be a better way to do this, but for now, I'd like to learn more about building dynamic SQL using looping."
Looping Through An Array To Create A Query 
Posted - 2002-02-14 : 11:01:49
