SQL Server Performance Forum – Threads Archive
Recursive UDF?
Hi All, Been hitting a bit of a brick wall, I was wondering if it’s possible to have a UDF or SP and in it use recursion to navigate a parent/child relationship on tables? I need to query a table and if nothing exists query the records parent id on the same table and so on and so on until I hit a record with a value that I’m looking for, theoretically there can be dozens of recursive calls required. Any ideas besides having a recursive function in the program and calling a SP recursively, I’d really like to do it in TSQL, btw anyone know of the performance penalties and how Sql Server manages the stack (if at all) when using recursion (that is if it can be done). Thanks in advance World Domination Through Superior Software<br />Couldn’t you also do this in a loop?<br /><br />set parentid = initialid<br />while ( value not what I want )<br />begin<br /> set @rowid = parentid<br /> select values, parentid from table where rowid = @rowid<br /> if @@rowcount = 0<br /> break<br />end<br /><br />now you have the values and @rowid is the id that gave it to you…?<br /><br />Cheers<br />Twan<br /><br />recursion does work, but there are limits to how many levels deep you can go… not that I remember how many levels that was… <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /><br />
Thanks, I’ll try that, I’m not sure why I was so stuck on doing it recursively.
The limit for stored procs is 32 I think. Cheers
Shaun World Domination Through Superior Software
Sorry about the multiple replies, the server was hanging and the script timed out when I posted back and after a refresh there were 3 replies! Also everytime I sign in it says I’m logged in but still prompts me for my username and password when I post, I think the server was a bit touchy. Moderators, delete as you see fit. Cheers
Shaun World Domination Through Superior Software
Ok, ive tidied up your replies. <br />I wasnt sure about the 32 limit for stored procs to be honest, I knew it was limited for triggers.<br /><br /><pre><br />create procedure dbo.find_max_recursion_limit<br /> @baseint int<br />as<br />begin<br /> select @baseint = @baseint + 1<br /> print ‘> entering: ‘+CONVERT(Varchar(10), @baseint)<br /> exec find_max_recursion_limit @baseint<br /> print ‘< leaving: ‘+CONVERT(Varchar(10), @baseint)<br />end<br />go<br /><br />exec find_max_recursion_limit 0<br /></pre><br /><br />output <br /><br /><pre><br />.<br />.<br />.<br />> entering: 30<br />> entering: 31<br />> entering: 32<br />Server: Msg 217, Level 16, State 1, Procedure find_max_recursion_limit, Line 7<br />Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).<br /></pre><br /><br />But yes. Youre right. 32 recursions is a limit <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />
]]>