[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

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.
0
susnewyork
Asked:
susnewyork
2 Solutions
 
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
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now