usp_PartsOfSpeech_wordEssayCount.sql

CREATE PROCEDURE [dbo].[usp_PartsOfSpeech_wordEssayCount]
(
	@wordlistInput varchar(max)
)
AS

	--table to hold results
	declare @rtn table (
		word varchar(50),
		recCount int
	)
	--variable for current word
	declare @word varchar(50)
	--table to hold list of words, deleted from as we go
	declare @wordlist table ( word varchar(50) )

	--split the input and store it as a table
	insert into @wordlist select [value] from dbo.fn_splitList(@wordlistInput)
	
	--initialize all results to a 0-value
	insert into @rtn ( word, recCount ) 
	select word, 0 from @wordlist
	
	--for each word
	while exists ( select top 1 * from @wordlist)
	begin
		--get the next word
		select top 1 @word = word from @wordlist 
		UPDATE r SET
			recCount = recCount + t.cnt
		FROM @rtn r
		INNER JOIN
		(
			select word as w, count(1) as [cnt]
			FROM @rtn
			INNER JOIN tblImageInfo ON
				word = @word and
				contains(essay, @word)
			GROUP BY word
		) t ON
			t.w = r.word

		--delete the current record from table var
		delete from @wordlist where word = @word
	end
	
	select * from @rtn order by recCount desc

GO