LISTING 1: Create Procedure tsp_prj_activity_tree_build (Build)

(
  @parent_activity_code VARCHAR(10),
  @gtemp_table_name VARCHAR(64),
  @indent_string VARCHAR(255)
)
AS
  /* ---------------------------------------------------------------------
  Procedure that calls itself, i.e., uses recursion to build
  the entire tree and feed it into the global temporary table
  specified via @gtemp_table_name.

  Starts with the activity specified via @parent_activity_code,
  collects all activities using tsp_prj_activity_child_list
  procedure, parses through activity so collected
  and then calls itself again for each of them.
  ----------------------------------------------------------------------- */

  -- Calling procedure: tsp_prj_activity_tree_list
  -- Called procedure: tsp_prj_activity_child_list

  SET NOCOUNT ON

  -- For basic items in the activity list.
  DECLARE @activity_code VARCHAR(10)
  DECLARE @activity_description VARCHAR(255)
  DECLARE @planned_start_date datetime

  -- For storing Unicode SQL statements to be executed on the fly.
  DECLARE @sql_statement_string nvarchar(1024)

  -- For storing the list of child activities.
  DECLARE @activity_list_cursor CURSOR

  -- Initialize or increment the indent_string
  -- depending upon whether the activity specified
  -- as parent is NULL.
  -- Note that '&nbsp' implies a single space in HTML output.
  IF  @parent_activity_code IS NULL
    SET @indent_string = ''
  ELSE
    SET @indent_string = @indent_string + replicate('&nbsp',10)

  -- Get all the child activities of the activity specified
  -- as parent via @parent_activity_code. If NULL, then implies
  -- activities that don't have any parent. The list is obtained
  -- using tsp_prj_activity_child_list procedure in the cursor
  -- declared above.
  EXECUTE tsp_prj_activity_child_list @parent_activity_code,
                                                             
						@activity_list_cursor OUTPUT

  -- Loop to parse the list of activities obtained in cursor.
  WHILE( 1 = 1 )
  BEGIN
    -- Fetch the details for next activity.
    FETCH NEXT FROM @activity_list_cursor
               INTO @activity_code,
                        @activity_description,
                        @planned_start_date
             
    -- Check if fetch is valid, i.e., there are more records.
    IF @@FETCH_STATUS = -1 BREAK

    -- Check and replace single quotes in activity description.
    SET @activity_description = REPLACE(@activity_description,'''','')

    -- Construct SQL statement to insert activity details in
    -- activity description; includes indentation + description + date.
SET @sql_statement_string = 'INSERT INTO '+@gtemp_table_name+
                 ' VALUES('
                 +''''+ @activity_code + ''''
                 +','
                 +''''
                      + @indent_string
                      + @activity_code
                      +' '
                      + @activity_description
                      +' ('+convert( varchar, @planned_start_date )+')'
                 +''''
                 +')'

    -- For debugging only: SELECT @sql_statement_string       

    -- Execute the SQL & insert activity details.
    EXECUTE sp_executesql @sql_statement_string

    -- Calls itself, i.e., recurses using the current activity
    -- as parent to get all child nodes associated with it.
    EXECUTE tsp_prj_activity_tree_build  @activity_code,
                                                                  
							@gtemp_table_name,
                                                        @indent_string
  END

  -- Close the cursor.
  CLOSE @activity_list_cursor

  -- Deallocate the cursor.
  DEALLOCATE @activity_list_cursor

  RETURN