Recursion in SQL
-
I had an evil idea so I ran this script on my local SQL server database:
CREATE PROCEDURE TESTPROC
AS BEGIN
SELECT 1
EXEC TESTPROC
ENDWhen I executed the stored procedure, it stopped after 32 levels of nesting. My evil plan failed and I learnt something new today.
"It is easy to decipher extraterrestrial signals after deciphering Javascript and VB6 themselves.", ISanti[^]
-
I had an evil idea so I ran this script on my local SQL server database:
CREATE PROCEDURE TESTPROC
AS BEGIN
SELECT 1
EXEC TESTPROC
ENDWhen I executed the stored procedure, it stopped after 32 levels of nesting. My evil plan failed and I learnt something new today.
"It is easy to decipher extraterrestrial signals after deciphering Javascript and VB6 themselves.", ISanti[^]
If you change to a recursive CTE you can play around with
OPTION (MAXRECURSION 100)Wrong is evil and must be defeated. - Jeff Ello
-
I had an evil idea so I ran this script on my local SQL server database:
CREATE PROCEDURE TESTPROC
AS BEGIN
SELECT 1
EXEC TESTPROC
ENDWhen I executed the stored procedure, it stopped after 32 levels of nesting. My evil plan failed and I learnt something new today.
"It is easy to decipher extraterrestrial signals after deciphering Javascript and VB6 themselves.", ISanti[^]
@@NESTLEVEL (Transact-SQL) | Microsoft Docs[^]
Each time a stored procedure calls another stored procedure or executes managed code by referencing a common language runtime (CLR) routine, type, or aggregate, the nesting level is incremented. When the maximum of 32 is exceeded, the transaction is terminated.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
I had an evil idea so I ran this script on my local SQL server database:
CREATE PROCEDURE TESTPROC
AS BEGIN
SELECT 1
EXEC TESTPROC
ENDWhen I executed the stored procedure, it stopped after 32 levels of nesting. My evil plan failed and I learnt something new today.
"It is easy to decipher extraterrestrial signals after deciphering Javascript and VB6 themselves.", ISanti[^]
I will take over the world before you do. Just letting you know! ;P
A new .NET Serializer All in one Menu-Ribbon Bar Taking over the world since 1371!
-
I will take over the world before you do. Just letting you know! ;P
A new .NET Serializer All in one Menu-Ribbon Bar Taking over the world since 1371!
-
I had an evil idea so I ran this script on my local SQL server database:
CREATE PROCEDURE TESTPROC
AS BEGIN
SELECT 1
EXEC TESTPROC
ENDWhen I executed the stored procedure, it stopped after 32 levels of nesting. My evil plan failed and I learnt something new today.
"It is easy to decipher extraterrestrial signals after deciphering Javascript and VB6 themselves.", ISanti[^]
I saw something similar happen with a trigger making a change that was then running the same trigger again. The updates were extremely slow and I discovered that there was, like you have discovered, a point at which SQL Server gave up - when the recursion was 32 deep. You would hope that the SQL Server would just catch this sort of thing at compile time.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
It will take over thousands upon thousands! And it's only 10 years away around the corner!!!! :omg:
A new .NET Serializer All in one Menu-Ribbon Bar Taking over the world since 1371!
-
I had an evil idea so I ran this script on my local SQL server database:
CREATE PROCEDURE TESTPROC
AS BEGIN
SELECT 1
EXEC TESTPROC
ENDWhen I executed the stored procedure, it stopped after 32 levels of nesting. My evil plan failed and I learnt something new today.
"It is easy to decipher extraterrestrial signals after deciphering Javascript and VB6 themselves.", ISanti[^]
I resorted to a GOTO in an SQL query Loop that created a new transaction to work round a repetitive action with an unknown data set size. It was dirty, but worked. Glad I didn't try recursion as that would have definitely ended in tears by the sound of things! You can read about the problem here.....[Developing Automated Data Purge Solution](https://www.codeproject.com/Articles/689916/Developing-Automated-Data-Purge-Solution), but it basically looks like this;
LoopStart:
IF (@Count - @RowCountTotal) > @BatchSize
Begin
Begin Transaction
Delete Top (@BatchSize) From Comment Where CommentTime < @StartDate
Set @RowCountTotal = @RowCountTotal + @@RowCount
Commit Transaction
Goto LoopStart
End
Else
If (@Count - @RowCountTotal) > 0
Begin
Begin Transaction
Delete Top (@Count - @RowCountTotal) From Comment Where CommentTime < @StartDate
Set @RowCountTotal = @RowCountTotal + @@RowCount
Commit Transaction
EndDave Find Me On:Web|Youtube|Facebook|Twitter|LinkedIn Folding Stats: Team CodeProject
-
I had an evil idea so I ran this script on my local SQL server database:
CREATE PROCEDURE TESTPROC
AS BEGIN
SELECT 1
EXEC TESTPROC
ENDWhen I executed the stored procedure, it stopped after 32 levels of nesting. My evil plan failed and I learnt something new today.
"It is easy to decipher extraterrestrial signals after deciphering Javascript and VB6 themselves.", ISanti[^]
IMO, recursion is inherently dangerous, and must be avoided whenever possible. Moreover, when you must use recursion, you must also take steps to ensure that your recursive routine converges and unwinds its recursive stack. Every recursive routine must incorporate a test that stops the recursion in such a way that the recursion depth is self-limiting. This is true regardless of the programming language in which the recursion occurs. Whenever I encounter a recursion, my first thought is to investigate other algorithms that don't rely on recursion. More often than not, I have managed to succeed in finding one.
David A. Gray Delivering Solutions for the Ages, One Problem at a Time Interpreting the Fundamental Principle of Tabular Reporting
