| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2007-03-20 : 09:01:56
|
| Koroner writes "Hi all. I'm very new to SQL programming so when I started coding I already expected some problem like the one I now describe to present itself soon.I'm not an English native speaker, pardon me for making any mistake.I'm currently programming in ASP.NET using Visual Studio.I've defined a function which consists of one quite simple query calling itself in a recursive way. The problem occurrs when running a view to check whether the function works prorperly, since the "nesting limit exceeded" error (not exact words) pops up.I didn't know there was such a limit.Searching the Web I've learnt there is a way to modify this limit, that is using the OPTION clause and the MAXRECURSION hint.But the fact is whenever I add the OPTION clause to the SELECT of my function Visual Studio tells me that there is a syntax error near "OPTION" keyword. I'm sure VS is not recognizing the "MAXRECURSION" keyword, because there are no syntax errors and the word is not colored in blue (unlike other keywords).What puzzles me most is that I tried to define a recursive SELECT provided with OPTION clause within a view or an on-the-fly query and all works fine there.Thanks for any reply." |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-03-20 : 13:55:10
|
| Post your code, please.e4 d5 xd5 Nf6 |
 |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2007-03-20 : 23:25:11
|
| I believe MaxRecursion defaults to a number a bit larger than 32,000 in conjunction with SQL Server 2005... there is no such thing for SQL Server 2000 and is limited to only 32 recursions... whatever... the problem is that you cannot use SET in a function... pretty sure that also means that OPTION clauses won't work either.Since I don't have SQL Server 2005, please check Books Online under CREATE FUNCTION... THAT will explain all limitations of CREATE FUNCTION.--Jeff Moden |
|
|
|
Koroner
Starting Member
3 Posts |
Posted - 2007-03-21 : 03:28:47
|
| Thank you guys, I already "solved" the problem by myself while the site team was evaluating my post.Like Jeff said, I guess that MAXRECURSION just isn't allowed within functions.Then I've learnt about "common table expressions" which are the tool one is supposed to use within SQL Server 2005 when recursion is needed, and I'm using them for that particular task SQL trhowed an error for.Anyway I'm a bit disappointed, because I can't do recursion in a "free" way. The only case database programmers think you need recursion is that of retrieving hierarchical data. Everyone seems to be happy with CTE since they specifically addresses this problem.Such a view of recursion seems a little "ortodox" and close-minded to me. I came from a bit of Prolog programming and I had been trying on istinct to reproduce the logic programming style in SQL. So I was using the where conditions of a select statement as if they had been clauses to check (through the calling of bit-returning functions). Now I can't do that and my code looks much less clear and understandable with these "common table expressions", that were clearly meant to cover the hierachical-data-retrieval needs. |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2007-03-21 : 07:47:05
|
| The default recursion level for CTE's is 100 so you may need to set it higher.The view of distain for most forms of recursion by some is actually pretty valid... recursion can be very difficult to troubleshoot when something goes wrong.That, not-with-standing, what is it that you are trying to accomplish using recursion?--Jeff Moden |
|
|
|
Koroner
Starting Member
3 Posts |
Posted - 2007-03-21 : 09:02:31
|
| The CTE I've built works fine, did not even need to set maxrecursion to some number above the default settings.If you're willing to know what the snippet where I tried to do function-recursion looked like, here is a simplified version:ALTER FUNCTION dbo.Funct1(@P1 int, @P2 int)RETURNS bitASBEGINRETURN(SELECT *FROM Tab1WHERE Tab1.Field1 = @P2AND (dbo.Funct2(@P1, Tab1.Field2) = 'True' OR dbo.Funct1(@P1, Tab1.Field2) = 'True')Pardon me for "generalizing" the code, but names of the tables/fields would mean nothing to you not being related to anything real.Removed it from its context I know it makes very little sense tho. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-03-21 : 13:58:00
|
| The reason database developers shy away from recursion is because it is very inefficient for returning data compared to using a temp table.Expanding a binary tree using recursion will incur one execution per item, while doing it with a temp table may only incur one execution per level.So if your binary tree has five levels with five nodes each, that is 125 calls using recursion, or just five calls using a temp table.e4 d5 xd5 Nf6 |
|
|
|
Koroner
Starting Member
3 Posts |
Posted - 2007-03-22 : 02:36:50
|
| Yes, I forgot to tell you that before coming across CTE's solution I had already coded an iteration using temporary table(s), and it worked. The iteration "guard" (or "watch", don't know its name in English) "triggered" on one of the temporary tables being empty (= no record), since I was decreasing its records (actually copying them to another temp table) at every step.When programming I usually prefer recursion since it's much more readable and "genuine". The loop I had to come up with looked messy, although was just 4-5 statements long (that's due to queries). Luckily, common table expressions helped a lot reducing the code.As I mentioned before, SQL being a declarative programming language I tended to apply Prolog clauses style to it.Thanks for the hint about recursion and database performance, I've read it elsewhere on the web as well. |
|
|
|
|
"MAXRECURSION" keyword not recognized within function?