Link to home
Start Free TrialLog in
Avatar of susnewyork
susnewyorkFlag for United States of America

asked on

There has to be a cleaner way to write this stored procedure

Hi everyone,

I'm fairly new to using and creating stored procedures and was wondering how I could modify this stored procedure so that is isn't as long as a book. The SP sets each defined output variable to the top 1 value generated from the following query.

(SELECT top 1
	<< Insert column name here >>
     FROM   
     sftu_JOE_TX_PLAN_HX txp 
     FULL OUTER JOIN person p 
     ON txp.person_id=p.person_id
     LEFT OUTER JOIN patient_status ps 
     ON p.person_id=ps.person_id
     LEFT OUTER JOIN patient_documents pd 
     ON p.person_id=pd.person_id
     LEFT OUTER JOIN patient_provider pp 
     ON p.person_id=pp.person_id
     LEFT OUTER JOIN paq_signoff_history paq 
     ON pd.person_id=paq.person_id AND 
     pd.enc_id=paq.enc_id AND 
     pd.document_id=paq.item_id
     LEFT OUTER JOIN signatures s 
     ON pd.document_id=s.doc_id
     LEFT OUTER JOIN patient_encounter pe 
     ON pd.enc_id=pe.enc_id AND 
     pd.person_id=pe.person_id
     LEFT OUTER JOIN patient_status_mstr psm 
     ON ps.patient_status_id=psm.patient_status_id
     WHERE  
     paq.signoff_action='A' AND 
     pp.provider_id='ADB69024-4B7A-4B75-A9A6-BA2695632603' AND 
     psm.description='Active' AND 
     paq.create_timestamp>={ts '2012-03-20 00:00:01'} AND 
     (pd.template_file='bh_master_treatment' OR pd.template_file='sftubh_treatment_plan_review')
    and @pid=p.person_id
    order by 1 desc)

Open in new window


The only thing that changes within the query is the column value that I am setting to the variable.

This is the monster of a stored procedure currently in my database:


ALTER procedure [dbo].[JOE_Prev_TX_Plan]
(@pid varchar(36),
@docName varchar (50) output,
@prev_TX_Plan varchar(10) output,
@txp_plusTime varchar(10) output,
@admittedDate varchar(10) output,
@txp_HX varchar(10) output)

as

--------------------------------------------------------------------------------------------------------get last tx

SET @prev_TX_Plan = 
(SELECT top 1
	--pd.template_file,
    ((convert(varchar, convert(datetime, (SELECT max(v) 
       FROM (VALUES (paq.create_timestamp)
                            , (txp.init_tx_plan_signed)
                            , (txp.tx_plan_rev_signed_1)
                            , (txp.tx_plan_rev_signed_2)
                            , (txp.tx_plan_rev_signed_3)
                            , (txp.tx_plan_rev_signed_4)
                            , (txp.tx_plan_rev_signed_5)
                            , (txp.tx_plan_rev_signed_6)) AS value(v))), 112))) as [maxxDate]
     FROM   
     sftu_JOE_TX_PLAN_HX txp 
     FULL OUTER JOIN person p 
     ON txp.person_id=p.person_id
     LEFT OUTER JOIN patient_status ps 
     ON p.person_id=ps.person_id
     LEFT OUTER JOIN patient_documents pd 
     ON p.person_id=pd.person_id
     LEFT OUTER JOIN patient_provider pp 
     ON p.person_id=pp.person_id
     LEFT OUTER JOIN paq_signoff_history paq 
     ON pd.person_id=paq.person_id AND 
     pd.enc_id=paq.enc_id AND 
     pd.document_id=paq.item_id
     LEFT OUTER JOIN signatures s 
     ON pd.document_id=s.doc_id
     LEFT OUTER JOIN patient_encounter pe 
     ON pd.enc_id=pe.enc_id AND 
     pd.person_id=pe.person_id
     LEFT OUTER JOIN patient_status_mstr psm 
     ON ps.patient_status_id=psm.patient_status_id
     WHERE  
     paq.signoff_action='A' AND 
     pp.provider_id='ADB69024-4B7A-4B75-A9A6-BA2695632603' AND 
     psm.description='Active' AND 
     paq.create_timestamp>={ts '2012-03-20 00:00:01'} AND 
     (pd.template_file='bh_master_treatment' OR pd.template_file='sftubh_treatment_plan_review')
    and @pid=p.person_id
    order by 1 desc)
    
    SET @docName = 
(SELECT top 1
	pd.template_file
     FROM   
     sftu_JOE_TX_PLAN_HX txp 
     FULL OUTER JOIN person p 
     ON txp.person_id=p.person_id
     LEFT OUTER JOIN patient_status ps 
     ON p.person_id=ps.person_id
     LEFT OUTER JOIN patient_documents pd 
     ON p.person_id=pd.person_id
     LEFT OUTER JOIN patient_provider pp 
     ON p.person_id=pp.person_id
     LEFT OUTER JOIN paq_signoff_history paq 
     ON pd.person_id=paq.person_id AND 
     pd.enc_id=paq.enc_id AND 
     pd.document_id=paq.item_id
     LEFT OUTER JOIN signatures s 
     ON pd.document_id=s.doc_id
     LEFT OUTER JOIN patient_encounter pe 
     ON pd.enc_id=pe.enc_id AND 
     pd.person_id=pe.person_id
     LEFT OUTER JOIN patient_status_mstr psm 
     ON ps.patient_status_id=psm.patient_status_id
     WHERE  
     paq.signoff_action='A' AND 
     pp.provider_id='ADB69024-4B7A-4B75-A9A6-BA2695632603' AND 
     psm.description='Active' AND 
     paq.create_timestamp>={ts '2012-03-20 00:00:01'} AND 
     (pd.template_file='bh_master_treatment' OR pd.template_file='sftubh_treatment_plan_review')
    and @pid=p.person_id)
    
        SET @txp_plusTime = 
(SELECT top 1
	Convert(varchar, convert(datetime, (case when pd.template_file='sftubh_treatment_plan_review' then dateadd(dd, +90,
    (convert(varchar, convert(datetime, (SELECT max(v) 
       FROM (VALUES (paq.create_timestamp)
                            , (txp.init_tx_plan_signed)
                            , (txp.tx_plan_rev_signed_1)
                            , (txp.tx_plan_rev_signed_2)
                            , (txp.tx_plan_rev_signed_3)
                            , (txp.tx_plan_rev_signed_4)
                            , (txp.tx_plan_rev_signed_5)
                            , (txp.tx_plan_rev_signed_6)) AS value(v))), 101))
                            ) 
     when pd.template_file='bh_master_treatment' then 
     dateadd(dd, +30,
    (convert(varchar, convert(datetime, (SELECT max(v) 
       FROM (VALUES (paq.create_timestamp)
                            , (txp.init_tx_plan_signed)
                            , (txp.tx_plan_rev_signed_1)
                            , (txp.tx_plan_rev_signed_2)
                            , (txp.tx_plan_rev_signed_3)
                            , (txp.tx_plan_rev_signed_4)
                            , (txp.tx_plan_rev_signed_5)
                            , (txp.tx_plan_rev_signed_6)) AS value(v))), 101))
                            ) end)), 112)
     FROM   
     sftu_JOE_TX_PLAN_HX txp 
     FULL OUTER JOIN person p 
     ON txp.person_id=p.person_id
     LEFT OUTER JOIN patient_status ps 
     ON p.person_id=ps.person_id
     LEFT OUTER JOIN patient_documents pd 
     ON p.person_id=pd.person_id
     LEFT OUTER JOIN patient_provider pp 
     ON p.person_id=pp.person_id
     LEFT OUTER JOIN paq_signoff_history paq 
     ON pd.person_id=paq.person_id AND 
     pd.enc_id=paq.enc_id AND 
     pd.document_id=paq.item_id
     LEFT OUTER JOIN signatures s 
     ON pd.document_id=s.doc_id
     LEFT OUTER JOIN patient_encounter pe 
     ON pd.enc_id=pe.enc_id AND 
     pd.person_id=pe.person_id
     LEFT OUTER JOIN patient_status_mstr psm 
     ON ps.patient_status_id=psm.patient_status_id
     WHERE  
     paq.signoff_action='A' AND 
     pp.provider_id='ADB69024-4B7A-4B75-A9A6-BA2695632603' AND 
     psm.description='Active' AND 
     paq.create_timestamp>={ts '2012-03-20 00:00:01'} AND 
     (pd.template_file='bh_master_treatment' OR pd.template_file='sftubh_treatment_plan_review')
    and @pid=p.person_id
    order by 1 desc)
    
    set @admittedDate=
    (SELECT top 1
	 pp.eff_date
     FROM   
     sftu_JOE_TX_PLAN_HX txp 
     FULL OUTER JOIN person p 
     ON txp.person_id=p.person_id
     LEFT OUTER JOIN patient_status ps 
     ON p.person_id=ps.person_id
     LEFT OUTER JOIN patient_documents pd 
     ON p.person_id=pd.person_id
     LEFT OUTER JOIN patient_provider pp 
     ON p.person_id=pp.person_id
     LEFT OUTER JOIN paq_signoff_history paq 
     ON pd.person_id=paq.person_id AND 
     pd.enc_id=paq.enc_id AND 
     pd.document_id=paq.item_id
     LEFT OUTER JOIN signatures s 
     ON pd.document_id=s.doc_id
     LEFT OUTER JOIN patient_encounter pe 
     ON pd.enc_id=pe.enc_id AND 
     pd.person_id=pe.person_id
     LEFT OUTER JOIN patient_status_mstr psm 
     ON ps.patient_status_id=psm.patient_status_id
     WHERE  
     psm.description='Active' and @pid=p.person_id)
     
     set @txp_HX=
    (SELECT top 1
	  ((convert(varchar, convert(datetime, (SELECT max(v) 
       FROM (VALUES           (txp.init_tx_plan_signed)
                            , (txp.tx_plan_rev_signed_1)
                            , (txp.tx_plan_rev_signed_2)
                            , (txp.tx_plan_rev_signed_3)
                            , (txp.tx_plan_rev_signed_4)
                            , (txp.tx_plan_rev_signed_5)
                            , (txp.tx_plan_rev_signed_6)) AS value(v))), 112))) as [maxxDate]
     FROM   
     sftu_JOE_TX_PLAN_HX txp 
     FULL OUTER JOIN person p 
     ON txp.person_id=p.person_id
     LEFT OUTER JOIN patient_status ps 
     ON p.person_id=ps.person_id
     LEFT OUTER JOIN patient_documents pd 
     ON p.person_id=pd.person_id
     LEFT OUTER JOIN patient_provider pp 
     ON p.person_id=pp.person_id
     LEFT OUTER JOIN paq_signoff_history paq 
     ON pd.person_id=paq.person_id AND 
     pd.enc_id=paq.enc_id AND 
     pd.document_id=paq.item_id
     LEFT OUTER JOIN signatures s 
     ON pd.document_id=s.doc_id
     LEFT OUTER JOIN patient_encounter pe 
     ON pd.enc_id=pe.enc_id AND 
     pd.person_id=pe.person_id
     LEFT OUTER JOIN patient_status_mstr psm 
     ON ps.patient_status_id=psm.patient_status_id
     WHERE  
     psm.description='Active' and @pid=p.person_id)

Open in new window


There has to be a shorter method than the way I have this set up.
SOLUTION
Avatar of Hamed Nasr
Hamed Nasr
Flag of Oman image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial