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.
LVL 1
susnewyorkAsked:
Who is Participating?
 
lomo74Commented:
a little shorter:
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

SELECT	TOP 1
	@prev_TX_Plan = ((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))),
	@docName = pd.template_file,
	@txp_plusTime = 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
    
    
SELECT	TOP 1
	@admittedDate = pp.eff_date,
	@txp_HX = ((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)))
     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

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

I agree!

To be able to help:

Show your procedure using only 3 table.
List few records of each table
list the expected output.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.