Creating nested procedures with temporary tables

You need to take an extra step to create the procedures described above. You cannot create base_proc until select_proc exists, and you cannot create select_proc until the temporary table exists. Here are the steps:

  1. Create the temporary table outside the procedure. It can be empty; it just needs to exist and to have columns that are compatible with select_proc:

    select * into #huge_result from ... where 1 = 2
  2. Create the procedure select_proc, as shown above.

  3. Drop #huge_result.

  4. Create the procedure base_proc.