July 29, 2003 at 6:25 am
I have two temporary tables (in sessions), which gets called in a stored procedure. Problem is when the session ends and sp gets executed I get an error message. If I put temporary tables in store procedure then I am not sure how to join two-stored procedure. According to my understanding, stored procedures cannot be joined... Wondering if there is a work around for my problem.
Thanks!
July 29, 2003 at 6:54 am
What is the error message you are getting, or is it possible for you to post some of your procedure code?
July 29, 2003 at 7:25 am
Here is the error message"Server: Msg 208, Level 16, State 1, Line 40
Invalid object name '##NumofEvents'."
when I run the stored procedure. And here is the stored procedure code:
select distinct p.people_id 'People ID', st.site_name 'Site Name',e.name 'Ethnicity',
CASE WHEN gender = 'M' THEN 'MALE' WHEN gender = 'F' THEN 'FEMALE' ELSE 'DECLINED' END 'Gender',
ep.intake_rating_fk 'Intake Rating',
ep.closure_rating_fk 'Closure Rating', convert(varchar, ep.closure_date, 101) 'Episode Closure Date',
ort.name 'Outcome Referral', rr.name 'OutCome Referral Reason',
max(cltyp.name) 'Call Type', cr.name 'Relationship', max(JobTyp.lookup_value) 'Job Type', max(ssl.years_of_service) 'Year of Service',
max(jdf.name) 'Job Dysfunction', max(ft.name) 'Follow Up Type',noe.NumofEvents 'Num of Events',
from people p
left outer join cases c ON p.people_id = c.people_fk
left outer join v_cases_type cst ON cst.v_ct_id = c.cases_type_fk
left outer join episode ep ON c.cases_id = ep.cases_fk
left outer join service svc ON ep.episode_id = svc.episode_fk
left outer join v_outcome_referral_type ort ON ort.v_ort_id = ep.outcome_referral_type_fk
left outer join v_outcome_referral_reason rr ON rr.v_orr_id = ep.outcome_referral_reasON_fk
left outer join v_ethnic_group e ON p.ethnic_group_fk = e.v_eg_id
left outer join call cl ON cl.call_id = svc.call_fk
left outer join v_call_type cltyp ON cltyp.v_ct_id = cl.call_type_fk
left outer join site st ON st.site_id = p.site_fk
left outer join contract_product_site cps ON st.site_id = cps.site_fk
left outer join contract_product cp ON cp.contract_product_id = cps.contract_product_fk
left outer join contract cc ON cc.contract_id = cp.contract_fk
left outer join referral ref ON ref.referral_id = svc.referral_fk
left outer join v_referral_type rt ON rt.v_rt_id = ref.referral_type_fk
left outer join v_referral_detail_type rdt ON rdt.v_rdt_id = ref.referral_detail_type_fk
left outer join event_note en ON cl.call_id = en.call_fk
left outer join v_event_note_type ent on ent.v_ent_id = en.note_type_fk
left outer join note_followup nf ON en.event_note_id = nf.event_note_fk
left outer join v_followup_type ft ON ft.v_ft_id = nf.followup_type_fk
left outer join vw_service_ssl ssl ON ep.episode_id = ssl.episode_fk
left outer join ##JobType JobTyp ON p.people_id = JobTyp.people_id
left outer join v_job_dysfunction jdf ON jdf.v_jd_id = svc.job_dysfunction_fk
left outer join v_contract_relation cr ON cr.v_cr_id = svc.contract_relatiON_fk
left outer join authorizations auth ON auth.authorizations_id = svc.authorizations_fk
left outer join ##NumofEvents noe ON p.people_id = noe.people_id
/*left outer join vw_NumofEvents noe ON p.people_id = noe.people_id*/
/*NumofEvents*/
where st.site_name like 'pfizer%' and ep.closure_date >= @month_start_date and ep.closure_date < @month_end_date
and c.status_fk='2' and p.deactivate is null and st.deactivate is null
and c.deactivate is null and svc.deactivate is null and ref.deactivate is null
and ep.deactivate is null
AND ((cltyp.name= 'Clinical' )
OR en.note_type_fk='29'
OR ft.name = 'Discontinued'
OR ft.name = 'Completed'
OR ref.referral_id is not null )
group by
p.people_id, p.gender, e.name,
ep.closure_date, ep.intake_rating_fk, svc.episode_fk,
ep.closure_rating_fk, st.site_name,
ort.name, rr.name,
rt.name, rdt.name, ref.accepted, ref.referral_id,
cr.name, noe.NumofEvents
order by st.site_name, p.people_id
July 29, 2003 at 7:45 am
Why is the stored procedure being executed AFTER the session ends and not BEFORE? Remember, ## temporary tables mean that all users (# means only creating user) can see the temporary table for the duration of the session which creates and destroys that temporary table. ## does not mean that the temporary table exists beyond the duration of the creating session. Could this be your issue? If so, create a permanent table, or execute the stored procedure before the session gets destroyed.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply