February 23, 2009 at 1:18 pm
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
(
selectid,
itemNbr = 1,
startPos = 1,
endpos = CHARINDEX( ',', list + ',') - 1
from @Empslist
UNION ALL
selectprv.id,
prv.itemNbr + 1,
prv.endPos + 2,
CHARINDEX( ',', curr.list + ',', prv.endPos + 2 ) - 1
fromSplitCTE prv INNER JOIN
@Empslist curr ON
prv.id = curr.id AND
CHARINDEX( ',', curr.list + ',', prv.endPos + 2 ) > 0
)
insert into @EmpiDs
selectitemNbr,
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
February 23, 2009 at 2:27 pm
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.
February 23, 2009 at 2:28 pm
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://www.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]
February 24, 2009 at 5:45 am
Tally table method is the best way to do things like this.
"Keep Trying"
February 24, 2009 at 5:54 am
Where is the stored procedure being called from?
If it is from another stored procedure then why create the list and then seperate them?
You can use a temp table to store the list and then query that from your called stored procedure.
Just remember to add some checking to make sure the temp table exists.
February 25, 2009 at 7:09 am
The default recursion limit is 100.
You need to add Option (MaxRecursion 32000) to the Query
You can choose a number lower than 32000, but must be at least equal to the number of delimiters in your string.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply