Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

TSQL Lab 11 - Writing a recursive procedure to update the count of child items under each parent category

Sep 23 2008 7:55AM by Jacob Sebastian   

In the previous lab, we discussed a use case where we need a recursive procedure to update the count of child items under each parent category. Let us try to achieve this by using a recursive stored procedure.

Let us write two stored procedures to achieve this. The first stored procedure will loop through all the TOP level categories and initiate the second procedure (recursive) for each top level category. The second procedure (recursive) will recursively update all the sub-categories under the given category.

Here is the script of the first procedure that loops over each top level category and triggers the recursive procedure. (Before you run this script, make sure that you have created the required tables and populated them using script given in the previous post.)

/*
EXECUTE UpdateItemCount @Debug = 1
*/
-- --------------------------------------------------------
-- This procedure will launch the update process. It will
-- start a recursive update for each top level category
-- --------------------------------------------------------
CREATE PROCEDURE [dbo].[UpdateItemCount]
(
    @Debug BIT = 0
)
AS
SET NOCOUNT ON

-- --------------------------------------------------------
-- Create a memory table to hold all the top level category
-- records and populate it.
-- --------------------------------------------------------
DECLARE @t TABLE (
    AutoID INT IDENTITY,
    CategoryID INT )
    
INSERT INTO @t (CategoryID)
SELECT CategoryID FROM Categories WHERE ParentID IS NULL

-- --------------------------------------------------------
-- counter variables for the LOOP
-- --------------------------------------------------------
DECLARE @cnt INT, @max INT
DECLARE @CategoryID INT

SELECT @cnt = 1, @max = COUNT(*) FROM @t

-- --------------------------------------------------------
-- Let us LOOP through each top level category
-- --------------------------------------------------------
WHILE @cnt <= @max BEGIN
    SELECT @CategoryID = CategoryID FROM @t WHERE AutoID = @cnt
    
    -- --------------------------------------------------------
    -- Launch the recursive procedure to update each TOP 
    -- level category
    -- --------------------------------------------------------
    EXECUTE RecursiveUpdateItemCount 
        @CategoryID = @CategoryID,
        @itemCount = 0,
        @level = 0,
        @debug = @debug
    
    SELECT @cnt = @cnt + 1
END

-- --------------------------------------------------------
-- Update Completed!!!
-- --------------------------------------------------------
IF @debug = 1 BEGIN
    PRINT 'Update Completed'
END

Now let us see the next procedure that recursively updates each category. Here is the script of the procedure.

CREATE PROCEDURE [dbo].[RecursiveUpdateItemCount]
(
    @CategoryID INT,
    @itemCount INT OUTPUT,
    @level INT,
    @Debug BIT = 0
)
AS
SET NOCOUNT ON

-- --------------------------------------------------------
-- For debugging purpose only
-- --------------------------------------------------------
DECLARE @catName VARCHAR(100)
SELECT @catName = CategoryName 
    FROM Categories 
    WHERE CategoryID = @CategoryID
    
IF @debug = 1 BEGIN
    PRINT SPACE(@level * 4) + 'Calculating... ' + @catName
END

-- --------------------------------------------------------
-- Create a memory table to populate the children of the 
-- category being processed
-- --------------------------------------------------------
DECLARE @t TABLE (
    AutoID INT IDENTITY,
    idCategory INT )
    
INSERT INTO @t (idCategory)
SELECT CategoryID 
    FROM Categories 
    WHERE ParentID = @CategoryID

-- --------------------------------------------------------
-- Counter variables
-- --------------------------------------------------------
DECLARE @cnt INT, @max INT
SELECT @cnt = 1, @max = COUNT(*) FROM @t
SELECT @level = ISNULL(@level,0) + 1

IF @max = 0 BEGIN
    -- --------------------------------------------------------
    -- If there are no child categories under the current 
    -- category, it is a leaf level category. Count the number
    -- of items under this category.
    -- --------------------------------------------------------
    SELECT @itemCount = COUNT(*) 
    FROM Products
    WHERE CategoryID = @CategoryID
END ELSE BEGIN
    DECLARE @childCategoryID INT
    DECLARE @tmpCount INT
    -- --------------------------------------------------------
    -- Run a LOOP over each child category
    -- --------------------------------------------------------
    WHILE @cnt <= @max BEGIN
        SELECT @tmpCount = 0
        
        SELECT @childCategoryID = idCategory 
        FROM @t 
        WHERE AutoID = @cnt

        -- --------------------------------------------------------
        -- Start RECURSION
        -- --------------------------------------------------------
        EXECUTE RecursiveUpdateItemCount 
            @CategoryID = @childCategoryID,
            @itemCount = @tmpCount OUTPUT,
            @level = @level,
            @debug = @debug
            
        SELECT @itemCount = ISNULL(@itemCount,0) + ISNULL(@tmpCount,0)
        
        SELECT @cnt = @cnt + 1
    END
END

-- --------------------------------------------------------
-- Print debugging information
-- --------------------------------------------------------
IF @debug = 1 BEGIN
    PRINT 
        SPACE((@level-1) * 4) 
        + 'Updated..' 
        + @catName 
        + '(' + CAST( @itemCount AS VARCHAR) + ')'
END

-- --------------------------------------------------------
-- For debugging purpose only
-- --------------------------------------------------------
UPDATE Categories SET 
    itemCount = @itemCount 
WHERE CategoryID = @CategoryID

Now, let us try to run this procedure and update the item count. Note that we have a debug flag in the stored procedure. This will print debugging information to the output window and we can examine that to make sure that the update is executed correctly. Run the following script to run the update.

EXECUTE UpdateItemCount @Debug = 1

This will update the categories recursively. Since we have set the debug flag to 1, it will print the following to the output window.

Calculating... Books
    Calculating... Computers
        Calculating... SQL Server
            Calculating... TSQL Programming
            Updated..TSQL Programming(2)
            Calculating... Performance Tuning
            Updated..Performance Tuning(1)
            Calculating... SSRS
            Updated..SSRS(1)
            Calculating... SSIS
            Updated..SSIS(1)
        Updated..SQL Server(5)
        Calculating... ASP.NET
        Updated..ASP.NET(2)
    Updated..Computers(7)
    Calculating... Fiction
    Updated..Fiction(2)
Updated..Books(9)
Calculating... Software
    Calculating... Tools and Utilities
    Updated..Tools and Utilities(1)
    Calculating... Games
        Calculating... XBox 360
        Updated..XBox 360(1)
        Calculating... Windows XP
        Updated..Windows XP(2)
        Calculating... Windows Vista
        Updated..Windows Vista(1)
    Updated..Games(4)
Updated..Software(5)
Update Completed

Let us check the table and see if the update is done correctly.

SELECT * FROM Categories
/*
CategoryID  CategoryName         ParentID    ItemCount
----------- -------------------- ----------- -----------
1           Books                NULL        9
3           Computers            1           7
5           SQL Server           3           5
7           TSQL Programming     5           2
8           Performance Tuning   5           1
9           SSRS                 5           1
10          SSIS                 5           1
6           ASP.NET              3           2
4           Fiction              1           2
2           Software             NULL        5
11          Tools and Utilities  2           1
12          Games                2           4
13          XBox 360             12          1
14          Windows XP           12          2
15          Windows Vista        12          1
*/

Note that we got the same results we discussed in the previous post. We recursively processed child categories. The example I presented above was very simple and we needed only 3 levels of recursion. In real life, there may be requirement for more levels of recursion. The maximum level of recursion permitted in a stored procedure is 32. So what do we do if we need to process more than 32 levels?

Let us see how to achieve this in the next post.

SEE ALSO

  1. TSQL Lab 10 - Performing recursive updates in SQL Server
  2. TSQL Lab 11 - Writing a recursive procedure to update the count of child items under each parent category
  3. TSQL Lab 12 - Writing a recursive procedure to handle more than 32 levels
  4. TSQL Lab 14 - Performing a recursive update for more than 32 levels
  5. TSQL Lab 18 - Performing Recursive Updates using CTE
  6. TSQL Lab 20 - Performing recursive updates using a BOTTOM to TOP recursive CTE

Tags: TSQL, TSQL Labs, Recursion,


Jacob Sebastian
1 · 100% · 32220
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]