# Using a Recursive CTE to Generate a List

• izhar-azati (7/9/2013)

Why not to use CLR aggregate function that you can download from:

http://msftengprodsamples.codeplex.com/wikipage?title=SS2008%21String%20Split%20Table-Valued%20Function%20%28TVF%29&referringTitle=Home

Izhar Azati

Mostly because I'm not going to download and install a .msi file from the internet from an untrusted source by an author I don't know. π

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

• smarinova (7/9/2013)

Thank you π ! I will definitely try FOR XML PATH. I came up with the recursive CTE solution after reading about another application of recursive CTEs, but perhaps performance wise, FOR XML PATH is faster.

I absolutely agree with Chris M. on this one. It's very well written with appropriate graphics. Well done!

Shifting gears, I've not tested the recursive method that you've written for performance or resource usage, yet, but I agree that the FOR XML path will likely be quite a bit quicker.

Also, 32K isn't the max limit on recursive CTEs. It the max descreetly definable limit. If you set the max to 0, it could run forever.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

• Using a cursor:

`DECLARE myCursor cursor LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY`

` FOR SELECT OfficeId`

` FROM dbo.Office`

`DECLARE @OfficeId int, @CountyNames varchar(3000), @StateAbbr varchar(5)`

`SELECT @OfficeId OfficeId, @StateAbbr StateAbbr, @CountyNames CountyNames`

` INTO #Results WHERE 1=0`

`OPEN myCursor`

`FETCH NEXT FROM myCursor INTO @OfficeId`

`WHILE @@FETCH_STATUS = 0 BEGIN`

` SELECT @CountyNames = '' -- Initialise`

` SELECT @CountyNames = @CountyNames + ', ' + C.CountyName,`

` @StateAbbr = C.StateAbbr`

` FROM dbo.[County] C`

` INNER JOIN dbo.[OfficeCounty] OC`

` ON OC.CountyId= C.CountyId`

` AND OC.OfficeID = @OfficeId`

` ORDER BY C.CountyName ASC`

` INSERT INTO #RESULTS`

` SELECT @OfficeId, @StateAbbr, STUFF(@CountyNames, 1, 2, '')`

` FETCH NEXT FROM myCursor INTO @OfficeId`

`END`

`CLOSE myCursor`

`DEALLOCATE myCursor `

`SELECT * FROM #RESULTS ORDER BY 2`

`DROP TABLE #RESULTS `

`GO`

I know none of FOR XML, rCTE's and cursors perform that well so it would be interesting to see what method is better.

• There are many ways to solve a problem. Some are better suited to situations than others.

I think that the point of the article was to teach about recursive CTEs.

ATBCharles Kincaid

• There are many ways to solve a problem.

Exactly. No one mentioned UDFs but that is a possibility too. Not a very good one though given some of the excellent responses here.

CREATE FUNCTION [dbo].[udfMyConcatTitles]

(

@myString VARCHAR(255)

)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @STR VARCHAR(8000)

SELECT @STR = ISNULL(@str+' / ', '')

+ OtherTitles

FROM dbo.vwMyConcatTitles

WHERE ObjectNumber = @myString

RETURN @STR

END

• Thanks for the well written, clear, and simple tutorial. I use CTE's a lot as they've proven very useful when needing to manipulate data without write access in a database. I needed to do the opposite (parse a delineated list) and have tried recursion in the past, but the piece I was missing was the union of the anchor & recursive members of the CTE. With that tidbit from your article, and the counter example from a question on Experts Exchange (http://www.experts-exchange.com/Q_26033380.html), I dynamically pulled apart & was then able to link to a parsed field that end business users have been manually parsing in excel (I know, ouch). It's fast, fairly concise, and accurate.

Appreciate you & all the others who take the time to publish your knowledge!!! π

• jennym (7/15/2013)

Thanks for the well written, clear, and simple tutorial. I use CTE's a lot as they've proven very useful when needing to manipulate data without write access in a database. I needed to do the opposite (parse a delineated list) and have tried recursion in the past, but the piece I was missing was the union of the anchor & recursive members of the CTE. With that tidbit from your article, and the counter example from a question on Experts Exchange (http://www.experts-exchange.com/Q_26033380.html), I dynamically pulled apart & was then able to link to a parsed field that end business users have been manually parsing in excel (I know, ouch). It's fast, fairly concise, and accurate.

Appreciate you & all the others who take the time to publish your knowledge!!! π

I strongly recommend not using an rCTE to "parse a delineated list". It's slow and it's resource intensive.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

• Agree completely, but wasn't sure a better alternative (the excel method has been worse for users than the CTE method so far). What would you recommend, assuming no write access to the database?

• jennym (7/15/2013)

Agree completely, but wasn't sure a better alternative (the excel method has been worse for users than the CTE method so far). What would you recommend, assuming no write access to the database?

It's probably not the answer you're looking for but I'd recommend talking with the DBA to get some decent utility code promoted so people can do their job. π If that's not an option, perhaps finding a different company to work for... one that doesn't hogtie people trying to work. If that's not an option, then an effecient inline splitter is easy enough to make without resorting to rCTEs.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

• Jeff Moden (7/15/2013)

It's probably not the answer you're looking for but I'd recommend talking with the DBA to get some decent utility code promoted so people can do their job. π If that's not an option, perhaps finding a different company to work for... one that doesn't hogtie people trying to work. If that's not an option, then an effecient inline splitter is easy enough to make without resorting to rCTEs.

While the 'find another job' advice seems a bit drastic π I actually had a note to self already to work with our dba to get a utility in place for splitting. Do you have any preferred examples for inline splitter's though, as I would be curiuos (maybe posted in another question or blog somewhere)? I read your Tally OH article but wasn't confident that's what you are driving at with your answer (and after that was strangely craving beer popsicles... ). Promise I'll drop it after this... appreciate the opportunity to pick your brain.

• Jeff Moden (7/15/2013)

jennym (7/15/2013)

Agree completely, but wasn't sure a better alternative (the excel method has been worse for users than the CTE method so far). What would you recommend, assuming no write access to the database?

It's probably not the answer you're looking for but I'd recommend talking with the DBA to get some decent utility code promoted so people can do their job. π If that's not an option, perhaps finding a different company to work for... one that doesn't hogtie people trying to work. If that's not an option, then an effecient inline splitter is easy enough to make without resorting to rCTEs.

Wow! I feel that way on many days. Give me the tools and stay out of the way. Then I get confronted with a client database where I have no change authority and can only get at data through stored procedures. Frankly (and bluntly) it sucks.

In cases like that I'm glad that I can write apps that use SQL as a resource and do all my string stuff in C#.

ATBCharles Kincaid

• jennym (7/16/2013)

Jeff Moden (7/15/2013)

It's probably not the answer you're looking for but I'd recommend talking with the DBA to get some decent utility code promoted so people can do their job. π If that's not an option, perhaps finding a different company to work for... one that doesn't hogtie people trying to work. If that's not an option, then an effecient inline splitter is easy enough to make without resorting to rCTEs.

While the 'find another job' advice seems a bit drastic π I actually had a note to self already to work with our dba to get a utility in place for splitting. Do you have any preferred examples for inline splitter's though, as I would be curiuos (maybe posted in another question or blog somewhere)? I read your Tally OH article but wasn't confident that's what you are driving at with your answer (and after that was strangely craving beer popsicles... ). Promise I'll drop it after this... appreciate the opportunity to pick your brain.

You can incorporate the DelimitedSplit8K function as inline code instead of a function. Just take the code from the function and use it in a CROSS APPLY.

Of course, the best thing to do would be to get the DBA to put the function into production... especially if you provide a beer popsicle as a peace offering. π

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

• Thanks!

• thank you!

It is faster than a cursor and it's simpler to implement. The cursor processes one row at a time.

using a quirky update in a function - what do you mean by that?

• smarinova (7/23/2013)

thank you!

It is faster than a cursor and it's simpler to implement. The cursor processes one row at a time.

using a quirky update in a function - what do you mean by that?

rCTE's process one row at a time too, off the stack, which is quite different to cursor processing and quite a bit quicker.

Here's a quirky update in a multistatement tvf. I wrote it with Dwain Camps a while back as a proof-of-concept; a set-based way to apply the same function n times to a variable, something like a nested REPLACE.

`ALTER FUNCTION [dbo].[IMF_QU]`

`-- multistatement tvf with quirky update`

`(`

`@amount DECIMAL(28,2)`

`)`

`RETURNS @Results TABLE (strFormattedAmount VARCHAR(44))`

`WITH SCHEMABINDING`

`AS`

`BEGIN `

`DECLARE @ReverseAmount VARCHAR(44);`

`SET @ReverseAmount = REVERSE(CONVERT(VARCHAR(34),(@amount)));`

`DECLARE @MappingTable TABLE (n TINYINT UNIQUE);`

`INSERT INTO @MappingTable (n)`

`SELECT n FROM (VALUES (7),(11),(15),(19),(23),(27),(31),(35),(39),(43)) d (n)`

`WHERE n <= (((LEN(REPLACE(@ReverseAmount,'-',''))-1)/3)*4)-1; `

`UPDATE @MappingTable SET @ReverseAmount = STUFF(@ReverseAmount,n,0,',') `

`INSERT INTO @Results SELECT REVERSE(@ReverseAmount)`

`RETURN`

`END`

`GO`

`SELECT *`

`FROM (VALUES (3756.84),(3756),(375),(4884215.00),(21488.81),(22),(48955547787899554522)) d (Amount)`

`CROSS APPLY dbo.IMF_QU (Amount)`

βWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.β - Gail Shaw