CTE Recursion Limitation

Question

Post reply

CTE Recursion Limitation

ishaan99

SSCertifiable

Points: 6261
More actions
February 23, 2009 at 1:18 pm

#155317

I have a set of employeids which is comma seperated and passed s parameter in a stored proc. If i take more than 100 empID it errors out saying "The maximum recursion 100 has been exhausted before statement completion". Is there a way we could set much more as a limit. the now of emplid could go even upto 7K which means i 'll have to change the CTE to use varchar(max) . Isthis comething doable. If so can someone please provide some input on this. TIA

declare @EmpiD varchar(max)

select @EmpiD = '100010,100104,100127,100137,100170,100216,100257,100393,100423,100438,100501,100521,100580,100629,100643,100646,100653,100659,100662,100701,100735,100831,100885,100888,100908,100921,100950,100957,100958,100960,100961,100962,100965,100971,100976,100978,100980,100983,100987,100988,100992,100997,100999,101003,101005,101006,101013,101019,101021,101025,101028,101029,101030,101035,100010,100104,100127,100137,100170,100216,100257,100393,100423,100438,100501,100521,100580,100629,100643,100646,100653,100659,100662,100701,100735,100831,100885,100888,100908,100921,100950,100957,100958,100960,100961,100962,100965,100971,100976,100978,100980,100983,100987,100988,100992'

--,100997,100999,101003,101005,101006,101013,101019,101021,101025,101028,101029,101030,101035'

DECLARE @Empslist TABLE( id integer, list varchar( 3000 ) );

insert into @Empslist select 1, @EmpiD;

-- This will be the temp table you will use to link your base tables to

DECLARE @EmpiDs TABLE( itemNbr smallint, EmpiD int );

-- Use a recursive Common Table Expression in order to split out the individual EmpiDs and place them into

-- a temp table you can link to ( @EmpiDs ).

with SplitCTE as

(

select id,

itemNbr = 1,

startPos = 1,

endpos = CHARINDEX( ',', list + ',') - 1

from @Empslist

UNION ALL

select prv.id,

prv.itemNbr + 1,

prv.endPos + 2,

CHARINDEX( ',', curr.list + ',', prv.endPos + 2 ) - 1

from SplitCTE prv INNER JOIN

@Empslist curr ON

prv.id = curr.id AND

CHARINDEX( ',', curr.list + ',', prv.endPos + 2 ) > 0

)

insert into @EmpiDs

select itemNbr,

CAST( SUBSTRING( list, startPos, endPos - startPos + 1 ) as INT ) as EmpiD

from @Empslist f INNER JOIN

SplitCTE s ON

f.id = s.id;

select * into #EmpiD from @EmpiDs

select * from #EmpiD

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 1

andrewd.smith

SSCrazy Eights

Points: 8444

February 23, 2009 at 2:27 pm

#948993

There are several other ways of splitting a delimited string, some of which are more efficient than using recursive CTEs.

You could use a Tally table, convert to an XML string and use XQuery, or simply use a WHILE loop in a UDF.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Yes, there is a way to do this, but you should not do it. In fact you should not being using this approach at all: Using Recursion as a proxy for Iteration is guaranteed bad performance in every language that I know of (except Lisp).

If you need to split a string like this, you should use a Tally/Numbers table as explained by this really smart guy in this really fine article:http://qa.sqlservercentral.com/articles/T-SQL/63003/

(ps: if you could edit your original post and put some spaces or line breaks between those commas, we could all read this much easier.)

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]