WEB LISTING 1: Create Procedure Tsp_prj_activity_tree_list (Tree List)

AS
  /* ---------------------------------------------------------------------
  called by the client which in our case is an ASP script
  the procedure returns the entire tree in form of a
  a resultset with proper indenting.
  ------------------------------------------------------------------------ */
  
  -- Calling procedure: Client program
  -- Called procedure: tsp_prj_activity_tree_build   
  
  SET NOCOUNT ON
  
  -- for storing the global temp table name
  DECLARE @gtemp_table_name VARCHAR(64)
  
  -- for storing Unicode SQL statements to be executed on the fly
  DECLARE @sql_statement_string nvarchar(1024)
  
  -- to keep the atomicity of entire operation
  BEGIN TRANSACTION
  
  -- Get a unique name for table using connection-specific @@spid
  -- provided by SQL Server. Table will be unique for each
  -- connection and will persist across procedures until the
  -- life span of connection or explicit DROP.
  SET @gtemp_table_name = '##prj_activity_tree_table'
                          +convert( varchar(4), @@spid )
  
  -- Construct SQL statement for creating global temp table.
  SET @sql_statement_string = 'CREATE TABLE ' + @gtemp_table_name
                             +' ('
                             +' activity_code VARCHAR(10) NOT NULL,'
                             +' activity_description VARCHAR(512) NOT NULL'
                             +' )'
  
  -- Execute SQL to create table.
  EXECUTE sp_executesql @sql_statement_string
  
  -- Call tsp_prj_activity_tree_build to build the tree.
  -- Note that parent activity code is passed as NULL.
  EXECUTE tsp_prj_activity_tree_build NULL, @gtemp_table_name, ''
  
  -- The tree is now ready in the temp table. 
  -- Construct SQL statement for selecting from table.
  SET @sql_statement_string = 'SELECT * FROM ' + @gtemp_table_name
                             
  -- Execute SQL to select.
  EXECUTE sp_executesql @sql_statement_string
  
  -- Construct SQL statement for dropping the temp table.
  SET @sql_statement_string = 'DROP TABLE ' + @gtemp_table_name
                             
  -- Execute SQL to drop the table.
  EXECUTE sp_executesql @sql_statement_string
  
  -- Complete the transaction.
  COMMIT TRANSACTION
  
  RETURN