Using one stored procedure to update table created in another?

SQL Apprentice Question
I have a stored procedures which creates a new table and then runs a
specific query. The results of said query are then inserted into the
newly created table. Now, I have another stored procedure which
should, in theory, update that new table with extra data.


I thought I could just do an update in the second sp but I'm hitting
some problems. Mainly, it's telling me that there's an invalid column
name, error 207, although I copied and pasted the name so the spelling
should match. It tells me that "observation_report_id" is the invalid
column name.


If it;s not the spelling of the name that's wrong, then what else could
be it be? Is there something else I'm missing that could bring up the
207 error?


If I can't get this working, could I do it with parameters? How would
I go about passing the unique key for each row from sp1 to sp2?


Thanks for any help,
-M-


Code example:


CREATE PROCEDURE frm_CNE_BasicInfo_pt2
AS


/** run query and insert results **/
update tbl_CNE_BasicInfo
set mainname=[mainname],
heading=[heading],
title=[title],
where obsRepID=observation_report_id


Select obstable.n_number, obstable.taskname as taskname,
obstable.observation_report_id, obstable.stream_id, mainname.mainname,
heading.heading, title.title
from (select p.n_number, tt.name as taskname,
orr.observation_report_id, pp.stream_id
from
parent p, parentpast pp, pastor pr, observationreport orr, trequest tr,
ttype tt
where p.parent_id = pp.parent_id
and pp.pastor_id = pr.pastor_id
and p.record_status = 'A'
and p.parent_id = orr.parent_id
and p.parent_id = tr.parent_id
and orr.trequest_id = tr.trequest_id
and tr.ttype_id = tt.ttype_id
and pp.stream_id = tr.stream_id
)as obstable
left outer join
(select ao.observation_report_id, dt.name, ao.value as mainname
from
atomicobservation ao
inner join dtype dt
on dt.dtype_id=ao.dtype_id
where dt.name = 'mainname')
as mainname
on mainname.observation_report_id=obstable.observation_report_id


etc etc



Celko Answers

>> I have a stored procedure which creates a new table and then runs a specific query. The results of said query are then inserted into the newly created table. Now, I have another stored procedure which should, in theory, update that new table with extra data. <<


This is not how to write an RDBMS. The schema is supposed to be a data
model of some real world situation. Creating tables on the fly is like
elephants appearing out of the sky.

What you seem to be doing is mimicking a file system. Hang a scratch
tape, dump some data to it. In the next *procedural* step, update the
scratch tape. In declarative programming, we try to get the desired
results in one step.


Given column names like "heading" and "title", it looks like you are
formatting a report on the database instead of in your application.
File systems and 3GL programming languages blend data and application
code into the same module, but RDBMS is supposed to be a tiered
architecture.


You can probably kludge your way thru this, but I would re-think how
you code in SQL.