## SP

 Author Message reema.m SSC-Enthusiastic Group: General Forum Members Points: 103 Visits: 31 Comments posted to this topic are about the item SP ashwani24 Ten Centuries Group: General Forum Members Points: 1048 Visits: 126 but only at 32 levels. Ola L Martins-329921 SSC-Addicted Group: General Forum Members Points: 436 Visits: 188 The "Don't know" alternative should give you points, right? I mean, if you don't know, then the correct answer is actually "Don't know"... ;-) Hugo Kornelis SSC-Forever Group: General Forum Members Points: 45010 Visits: 13569 I love the "don't know" option, and I agree with the poster above that those who rightfully tick that box should get a point. ;-)In T-SQL, recursion is not as useful as it is in more traditional programming languages. Not only because of the 32-level restriction, but also because recursive algorithms are, by nature, iterative - and SQL Server is tuned to optimize set-based operations.For the factorial example, the code below is not only more succinct, it also woud perform a lot better - if SQL Server actually had an aggregate PRODUCT() function (which, unfortunately, is not the case):`SELECT PRODUCT(n)FROM dbo.NumbersWHERE n BETWEEN 1 AND @in;`However, because there is no PRODUCT function, we have to work around that - using the mathematical fact that ex * ey = ex+y. (Note that the formula below works only for numbers above zero; there are also formulas to calculate aggregate product for sets that may include negative numbers or zero - google or bing if you need them, or ask me).`SELECT EXP(SUM(LOG(n)))FROM dbo.NumbersWHERE n BETWEEN 1 AND @in;`I'd argue that, with a comment thrown in to explain the EXP(SUM(LOG())) bit, this is still easier to maintain than the recursive procedure - and it'll definitely be faster!* Note that the above is not meant to suggest that recursion has no place in SQL Server, I only wanted to point out that when possible, a set-based solution should be the first choice. There will always be situations where recursion is indeed the best solution. Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis paul s-306273 SSCertifiable Group: General Forum Members Points: 6890 Visits: 1354 This article was posted in 2005 on TechRepublic:[url=http://www.techrepublic.com/article/using-recursion-in-stored-procedures/5700193][/url] Hugo Kornelis SSC-Forever Group: General Forum Members Points: 45010 Visits: 13569 paul s-306273 (4/26/2012)This article was posted in 2005 on TechRepublic:[url=http://www.techrepublic.com/article/using-recursion-in-stored-procedures/5700193][/url]Ouch! That level of simularity is way beyond coinicidence... I'll shoot Steve an email now and ask him to deal with this IP infringement as soon as possible. Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis paul s-306273 SSCertifiable Group: General Forum Members Points: 6890 Visits: 1354 Hugo Kornelis (4/26/2012)paul s-306273 (4/26/2012)This article was posted in 2005 on TechRepublic:[url=http://www.techrepublic.com/article/using-recursion-in-stored-procedures/5700193][/url]Ouch! That level of simularity is way beyond coinicidence... I'll shoot Steve an email now and ask him to deal with this IP infringement as soon as possible.Hugo - thanks for that. kaspencer Hall of Fame Group: General Forum Members Points: 3471 Visits: 931 If the writer of the commentary on the answer to this question were one of my students, and if he were not named Arthur Fuller, I would submit his name to a student ethics committee on the grounds of plagiarism.As already mentioned, please read:[url=http://www.techrepublic.com/article/using-recursion-in-stored-procedures/5700193][/url]No obfuscation whatsoever was attempted when this piece of text was stolen. Apologies are demanded, and the writer should have all previously gained points confiscated.Kenneth Spencer You never know: reading my book: "All about your computer" might just tell you something you never knew!lulu.com/kaspencer Thomas Abraham SSCertifiable Group: General Forum Members Points: 7553 Visits: 2256 Ola L Martins-329921 (4/26/2012)The "Don't know" alternative should give you points, right? I mean, if you don't know, then the correct answer is actually "Don't know"... ;-)Sometimes true wisdom is just realizing what you DON'T know. (And humility follows closely behind.)I have never used recursive SPs. I can't think of situation where I might, given the environment in which I am. However, I absolutely love recursive CTE's, even if I could only use them for BOM (bill of materials) explosions. We had a previous implementation that used CURSORS to do them! I have also used recursive functions. They are handy for string parsing. I imagine that recursive CTE's would do a bit better performance wise, but I find it easier to write readable/maintainable code when done as a function.Thanks to OP for question! Please don't go. The drones need you. They look up to you.Connect to me on LinkedIn Tom Thomson SSC Guru Group: General Forum Members Points: 68171 Visits: 13204 On top of the plagiarism issue (as yet unproven -"reema" may be a pseudonym of Arthur Fuller, although that seems unlikely) and the use of technical terms (recursive, recursion) of computer science and mathematics in a sense that they don't have in those arts/sciences (despite SQL Server certainly being something to do with computation) the explanation is pretty terrible. Since SQL Server doesn't do multi-statement optimisation, it certainly doesn't do optimisation of tail-recursion; so quite clearly T-SQL procs that call themselves to implement tail-recursion should never be written since they will be gloriously inefficient - so this implementation of factorial is a fine example of something that should never be done. In addition, T-SQL doesn't actually support recursion except in a restricted form, with a restriction of 32 on nesting level ( and although this does no harm to the factorial function, which of course blows up at 21 even if it uses bigint for its result, that restriction should certainly have been mentioned. So in general a T-SQL procedure calling itself should be used only for functions which are not easily converted to tail-recursive form (and hence to iterative form) and where it is assured that the necessary nesting level does not excede the T-SQL limit or the procedure contains tests to detect when the nesting level is getting to high and can do something useful/intelligent to avoid this (instead of just failing with the vanilla error message provided by the system). A discussion of "recursive" stored procedures which doesn't point this out is a bit like rat poison in a jam-jar without a warning label.Note: in mathematics, in computer science, in formal logic, and in computation theory "recursive" means "able to be computed by using a Turing machine" or, equivalently, "able to be computed by using lambda calculus"; so every possible stored procedure is recursive, whether it calls itself or not. Use of the term to mean "calls itself" is fine in other contexts, but really irritating in the context of computing, as in this question and explanation. Generally it is believed that "recursive" is the same as "in principle a human being could compute it if supplied with enough paper and pencils and detailed instructions by following the instructions exactly and mechanically with no excercise of intelligence or intuition" (that is "Church's Thesis" or "Turing's Thesis" or "The Church-Turing Thesis", perhaps the most important idea in the theory of computation and certainly the most horribly misinterpreted - a decent explanation can be found here). Tom