CODEWITHSUNDEEP
sql-servert-sqlrecursion
user3735855
user3735855

Reputation: 144

MaxRecursion exceeded in TSQL sp

I have this code which works to a certain extent, but once there are too many records in the table it throws up the "recursive error message (over 100)"

My code counts the number of unique words in the column. I have added the "option (maxrecursion 0) text but this has made no difference. Can anyone help please?

I am 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 into #a
from [test_question] p

cross apply
(
  select substr from 
  dbo.f_split(p.answer, ' ')
) a 


left join
@matchlist m
on a.substr = m.name
where len(coalesce(m.replacement, a.substr))  >3
and coalesce(m.replacement, a.substr) not in ('they','with','have','been','were','house','from','isos','went','when','find','just','that','than','them','their','there')

group by coalesce(m.replacement, a.substr)

order by 2 desc

select * ,row_number()over (order by count desc) as ranking from #a
option (maxrecursion 0)
drop table #a

Upvotes: 2

Views: 360

Answers (1)

usr
usr

Reputation: 171206

You have placed option (maxrecursion 0) on a query which is not recursive. That's why it has no effect.

Probably, a function that you call is internally recursive. You must place option (maxrecursion 0) there.

Upvotes: 1

Related Questions