sql server - MaxRecursion exceeded in TSQL sp -

sql server - MaxRecursion exceeded in TSQL sp -

i have code works extent, 1 time there many records in table throws "recursive error message (over 100)"

my code counts number of unique words in column. have added "option (maxrecursion 0) text has made no difference. can help please?

i using sql server 2005(!)

my sp is:

declare @table table(name varchar(50)) insert @table values('bla bla bla ltd') insert @table values('bla plc ltd') insert @table values('more text ') declare @matchlist table(name varchar(50), replacement varchar(50)) insert @matchlist values('very good', 'good') insert @matchlist values('good.', 'good') insert @matchlist values('nice.', 'nice') insert @matchlist values('-nice', 'nice') insert @matchlist values('service.', 'service') insert @matchlist values('great.', 'great') insert @matchlist values('with.', 'with') insert @matchlist values('well.', 'well') insert @matchlist values('problems.', 'problems') --query select coalesce(m.replacement, a.substr) answer, count(*) count #a [test_question] p cross apply ( select substr dbo.f_split(p.answer, ' ') ) left bring together @matchlist m on a.substr = m.name len(coalesce(m.replacement, a.substr)) >3 , coalesce(m.replacement, a.substr) not in ('they','with','have','been','were','house','from','isos','went','when','find','just','that','than','them','their','there') grouping coalesce(m.replacement, a.substr) order 2 desc select * ,row_number()over (order count desc) ranking #a alternative (maxrecursion 0) drop table #a

you have placed option (maxrecursion 0) on query not recursive. that's why has no effect.

probably, function phone call internally recursive. must place option (maxrecursion 0) there.

sql-server tsql recursion