susnewyork
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.
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:
There has to be a shorter method than the way I have this set up.
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)
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)
There has to be a shorter method than the way I have this set up.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.