I need to count occurrence of a list of words across all records in a given table. If I only had 1 word, I could do this:

select count(id) as NumRecs where essay like '%word%'

But my list could be hundreds or thousands of words, and I don't want to create hundreds or thousands of sql requests serially; that seems silly. I had a thought that I might be able to create a stored procedure that would accept a comma-delimited list of words, and for each word, it would run the above query, and then union them all together, and return one huge dataset. (Sounds reasonable, right? But I'm not sure where to start with that approach...)

Short of some weird thing with union, I might try to do something with a temp table -- inserting a row for each word and record count, and then returning select * from that temp table.

If it's possible with a union, how? And does one approach have advantages (performance or otherwise) over the other?