WEB LISTING 2: Create Procedure Tsp_prj_activity_child_list (Child List)

(
  @parent_activity_code VARCHAR(10),
  @activity_list_cursor CURSOR VARYING OUTPUT
)
AS
  /* ---------------------------------------------------------------------
  Select all the child activities of the activity specified
  via @parent_activity_code. Only the immediate
  children are selected and the selection is directly fed 
  into the cursor passed on as parameter. 
  ------------------------------------------------------------------------ */
  
  -- Calling procedure: tsp_prj_activity_tree_build
  -- Called procedure: (none)

  SET NOCOUNT ON
  
  -- Initialize the cursor with proper select statement.
  SET @activity_list_cursor = CURSOR FOR
      SELECT activity_code,
                     activity_description,
                     planned_start_date
        FROM project_activity_schedule ps
       WHERE (@parent_activity_code IS NOT NULL AND ps.parent_activity_code = @parent_activity_code)
               OR ( @parent_activity_code IS NULL AND ps.parent_activity_code IS NULL )
    ORDER BY planned_start_date,
                       activity_code
             
  -- Populate the cursor.
  OPEN @activity_list_cursor
  
  RETURN