Recently, I had to create a SQL recursion to delete nested data. Recursion doesn't come around often, but when it does and you use it correctly, it's so good. Basically, you have a block of code that calls itself. For example, I had to use it because I had nested data, which means that I had a hierarchy of data that related to each other. I had the parent, the Item Type (IT), and then X number of Item Groups (IG). I cannot nest ITs, but I could nest IGs, so I might come up with a structure like so:
-IT
-IG1
-IG2
-IG3
-IG4
-IG5
-IG6
-IT
-IG7
-IG8
-IT
In the database, I have one table for each IG which has the unique field TCSID as the primary key. To show relationships in the table, rather then creating another linking table, I have another column called ParentTCSID, which would contain the parent IG's ID of the given IG. In the example above, that would mean that if IG2's TCSID is 12, then IG3's and IG4's ParentTCSID would be set to 12.
If one were to delete IG2, I would need to delete all children, that includes IG3,4,5,6. If we were only going one level deep, I could just run something like:
DELETE FROM IGTable WHERE ParentTCSID = 12
However, because IG5's ParentTCSID is actually the TCSID of IG4, that won't work.
So, we use recursion. Basically, we build a list of children of IG2, loop through them, delete them, then use the current child's TCSID to call on the same stored procedure to delete all the children for the current child, such as the following:
ALTER PROCEDURE [dbo].[usp_DeleteIG]
@TCSID int
AS
BEGIN
SET NOCOUNT ON;
--generate child list
DECLARE @child TABLE(ID bigint)
INSERT INTO @child
SELECT TCSID FROM IGTable WHERE ParentTCSID = @TCSID
--curs through
DECLARE childCurs CURSOR LOCAL
FOR SELECT ID FROM @child
OPEN childCurs
DECLARE @currID bigint
FETCH NEXT FROM childCurs INTO @currID
WHILE @@FETCH_STATUS = 0
BEGIN
--delete item
DELETE FROM IGTable WHERE TCSID = @currID
--recurs
EXEC usp_DeleteIG @currID
FETCH NEXT FROM childCurs INTO @currID
END
CLOSE childCurs
DEALLOCATE childCurs
--delete group
DELETE FROM IGTable WHERE TCSID = @TCSID
END
Note the red LOCAL in the code. This is the key to the entire post. If you were to run the above without it, you would get an error saying that the cursor childCurs is already open. This is because, by default, cursors are global, so when you open it on the first run then recurse, when you try to open it again, SQL Server already thinks it's open. By putting in the LOCAL keyword, the cursor only exists to the particular instance of the running code, thus allowing multiple cursors of the same name to be open at the same time.
Posts
