April 27, 2010 at 1:27 pm
I have a stored procedure that selects many fields and I pass it a year and month, then want the results for all id's. It also contains sub queries. I'm not getting it to work. If I pass a vst_ext_id in as a parameter it works great. I appreciate any ideas.
Do I have to use a cursor? Sorry this is so complicated!
CREATE PROCEDURE [dbo].[spvisitstuff]
@year int,
@month int
as
begin
set nocount on
declare@vstextid char(20)
SELECT TPM300_PAT_VISIT.vst_ext_id, TSM180_MST_COD_DTL.cod_dtl_ext_id, TSM180_MST_COD_DTL_2.cod_dtl_ext_id, TPM300_PAT_VISIT.dschrg_ts, TPM300_PAT_VISIT.med_rec_no, TPM300_PAT_VISIT.adm_ts, TPM300_PAT_VISIT.drg_cd, TPM318_VISIT_DIAGNOSIS.ICD9_diag_ty, TSM910_ICD9_REF.icd9_code, TPB200_BILLING_INVOICE.ttl_charges_billed_at, TPB200_BILLING_INVOICE.billing_date, TSM180_MST_COD_DTL_1.cod_dtl_ext_id, TSM180_MST_COD_DTL_3.cod_dtl_ext_id, TSM180_MST_COD_DTL_4.cod_dtl_ext_id, TSM180_MST_COD_DTL_5.cod_dtl_ext_id, TSM180_MST_COD_DTL_6.cod_dtl_ext_id, VMGH003_PATIENT.FNAME, VMGH003_PATIENT.LNAME, VMGH003_PATIENT.MI, VMGH003_PATIENT.STREET, VMGH003_PATIENT.CITY, VMGH003_PATIENT.STATE, VMGH003_PATIENT.ZIPCODE, VMGH003_PATIENT.PHONE, TSM180_MST_COD_DTL_7.cod_dtl_ext_id, VMGH003_PATIENT.DOB, VMGH003_PATIENT.SEX, TSM180_MST_COD_DTL_8.cod_dtl_ext_id, TSM040_PERSON_HDR.bth_ts, TSM180_MST_COD_DTL_9.cod_dtl_ext_id, VMGH003_PATIENT.SSN,
(SELECT TPM700_PAYOR_PLAN.plan_ext_id
FROM (paragon_test.dbo.TPM300_PAT_VISIT TPM300_PAT_VISIT INNER JOIN paragon_test.dbo.TPM311_VISIT_PAYOR TPM311_VISIT_PAYOR (NOLOCK)ON TPM300_PAT_VISIT.vst_int_id=TPM311_VISIT_PAYOR.vst_int_id) INNER JOIN paragon_test.dbo.TPM700_PAYOR_PLAN TPM700_PAYOR_PLAN (NOLOCK)ON TPM311_VISIT_PAYOR.plan_int_id=TPM700_PAYOR_PLAN.plan_int_id
WHERE TPM311_VISIT_PAYOR.pyr_seq_no=4981 and TPM300_PAT_VISIT.vst_ext_id = @vstextid) as payor_primary,
(SELECT TPM700_PAYOR_PLAN.plan_ext_id
FROM (paragon_test.dbo.TPM300_PAT_VISIT TPM300_PAT_VISIT INNER JOIN paragon_test.dbo.TPM311_VISIT_PAYOR TPM311_VISIT_PAYOR (NOLOCK)ON TPM300_PAT_VISIT.vst_int_id=TPM311_VISIT_PAYOR.vst_int_id) INNER JOIN paragon_test.dbo.TPM700_PAYOR_PLAN TPM700_PAYOR_PLAN (NOLOCK)ON TPM311_VISIT_PAYOR.plan_int_id=TPM700_PAYOR_PLAN.plan_int_id
WHERE TPM311_VISIT_PAYOR.pyr_seq_no=4978 and TPM300_PAT_VISIT.vst_ext_id = @vstextid) as payor_secondary
FROM ((((((((((((((paragon_test.dbo.TPM300_PAT_VISIT TPM300_PAT_VISIT INNER JOIN paragon_test.dbo.TSM180_MST_COD_DTL TSM180_MST_COD_DTL (NOLOCK)ON TPM300_PAT_VISIT.pat_cat_cd=TSM180_MST_COD_DTL.cod_dtl_int_id) INNER JOIN paragon_test.dbo.TSM040_PERSON_HDR TSM040_PERSON_HDR (NOLOCK)ON TPM300_PAT_VISIT.psn_int_id=TSM040_PERSON_HDR.psn_int_id) INNER JOIN paragon_test.dbo.TSM180_MST_COD_DTL TSM180_MST_COD_DTL_1 (NOLOCK)ON TPM300_PAT_VISIT.adm_ty=TSM180_MST_COD_DTL_1.cod_dtl_int_id) INNER JOIN paragon_test.dbo.TSM180_MST_COD_DTL TSM180_MST_COD_DTL_3 (NOLOCK)ON TPM300_PAT_VISIT.adm_src_cd=TSM180_MST_COD_DTL_3.cod_dtl_int_id) INNER JOIN paragon_test.dbo.TSM180_MST_COD_DTL TSM180_MST_COD_DTL_5 (NOLOCK)ON TPM300_PAT_VISIT.dschg_sta_cd=TSM180_MST_COD_DTL_5.cod_dtl_int_id) INNER JOIN paragon_test.dbo.TSM180_MST_COD_DTL TSM180_MST_COD_DTL_6 (NOLOCK)ON TPM300_PAT_VISIT.fin_cls_cd=TSM180_MST_COD_DTL_6.cod_dtl_int_id) LEFT OUTER JOIN paragon_test.dbo.VMGH003_PATIENT VMGH003_PATIENT (NOLOCK)ON TPM300_PAT_VISIT.vst_int_id=VMGH003_PATIENT.vst_int_id) LEFT OUTER JOIN paragon_test.dbo.TSM180_MST_COD_DTL TSM180_MST_COD_DTL_2 (NOLOCK)ON TPM300_PAT_VISIT.pat_ty=TSM180_MST_COD_DTL_2.cod_dtl_int_id) LEFT OUTER JOIN paragon_test.dbo.TPM318_VISIT_DIAGNOSIS TPM318_VISIT_DIAGNOSIS (NOLOCK)ON TPM300_PAT_VISIT.vst_int_id=TPM318_VISIT_DIAGNOSIS.vst_int_id) LEFT OUTER JOIN paragon_test.dbo.TPB200_BILLING_INVOICE TPB200_BILLING_INVOICE (NOLOCK)ON TPM300_PAT_VISIT.vst_int_id=TPB200_BILLING_INVOICE.vst_int_id) LEFT OUTER JOIN paragon_test.dbo.TSM180_MST_COD_DTL TSM180_MST_COD_DTL_4 (NOLOCK)ON TPM300_PAT_VISIT.adm_src_cd=TSM180_MST_COD_DTL_4.cod_dtl_int_id) LEFT OUTER JOIN paragon_test.dbo.TSM180_MST_COD_DTL TSM180_MST_COD_DTL_9 (NOLOCK)ON TPM300_PAT_VISIT.cny_cd=TSM180_MST_COD_DTL_9.cod_dtl_int_id) INNER JOIN paragon_test.dbo.TSM910_ICD9_REF TSM910_ICD9_REF (NOLOCK)ON TPM318_VISIT_DIAGNOSIS.icd9_int_id=TSM910_ICD9_REF.icd9_int_id) LEFT OUTER JOIN paragon_test.dbo.TSM180_MST_COD_DTL TSM180_MST_COD_DTL_7 (NOLOCK)ON TSM040_PERSON_HDR.mry_sta_cd=TSM180_MST_COD_DTL_7.cod_dtl_int_id) LEFT OUTER JOIN paragon_test.dbo.TSM180_MST_COD_DTL TSM180_MST_COD_DTL_8 (NOLOCK)ON TSM040_PERSON_HDR.rac_cd=TSM180_MST_COD_DTL_8.cod_dtl_int_id
WHERE NOT (TSM180_MST_COD_DTL.cod_dtl_ext_id='CXL' OR TSM180_MST_COD_DTL.cod_dtl_ext_id='PRE') AND NOT (TSM180_MST_COD_DTL_2.cod_dtl_ext_id='C' OR TSM180_MST_COD_DTL_2.cod_dtl_ext_id='L')and TPM300_PAT_VISIT.vst_ext_id = @vstextid and datepart(year, TPM300_PAT_VISIT.dschrg_ts) = @year and datepart(month, TPM300_PAT_VISIT.dschrg_ts) = @month
exec(@vstextid)
end
April 27, 2010 at 1:37 pm
There is no place in your code where you assign a value to @vstextid. Therefore, your WHERE clauses will most probably eleminate all rows.
Btw: what do you try to achieve with the "exec(@vstextid)" statement at the end of the proc?
April 27, 2010 at 1:46 pm
Not to ask a dumb question but since you are not looking for the vstextid parameter to come in have you tried removeing the where clauses that use the @vstextid. In theory if you removed those from the where clause it would then pull all id's. you may need to add a line to you inner queries though that would link the TPM300_PAT_VISIT.vst_ext_id in the inner query to the same field in the main query to link the correct id's.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 27, 2010 at 1:47 pm
exec(@vstextid): Guess I thought it was needed to run the code. It was similar to an example I was trying to use.
And you're right this executes but returns no rows, and there should be results.
I tried a couple ways to assign te value, example:
declare @vstextid = TPM300_PAT_VISIT.vst_ext_id
but that had an error: Cannot assign a default value to a local variable.
I thought it was being assigned down in the final where part:
and TPM300_PAT_VISIT.vst_ext_id = @vstextid
Do you have any ideas as to where I should assign it?
Thanks again for any ideas!
April 27, 2010 at 1:56 pm
What's the purpose of @vstextid?
If you would assign the value of TPM300_PAT_VISIT.vst_ext_id to it, your query would look like TPM300_PAT_VISIT.vst_ext_id=TPM300_PAT_VISIT.vst_ext_id which will return all rows...
Asking a little more in general: what do you expect this procedure should return?
April 27, 2010 at 2:12 pm
avoid dynamic sql if you can ....
all time ref: The curse and blessings of dynamic SQL http://www.sommarskog.se/dynamic_sql.html
But I don't think you need dynamic sql anyway.
So, skip the "declare @vstextid char(20)" which will be to small to contain your query string anyway.
Regarding your where clause, use the strength of your datetime datatype !
CREATE PROCEDURE [dbo].[spvisitstuff]
@refdate datetime
as
begin
declare @startdate datetime, @enddate datetime
Select @startdate = dateadd(mm, datediff(mm,0,@refdate),0) -- gives you day 1 of the month
select @enddate = dateadd(mm,1,@startdate) -- day 1 of next month
select .....
from ....
where ......
and TPM300_PAT_VISIT.dschrg_ts >= @startdate
and TPM300_PAT_VISIT.dschrg_ts < @enddate
-- if column dschrg_ts is indexed, that index can be used ! avoid functions on indexed columns.
You stored procedure will return this row set to your application's recordset or datatable object.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 27, 2010 at 2:26 pm
The purpose of @vstextid is to pass the TPM300_PAT_VISIT.vst_ext_id to the subqueries.
Maybe I'm making this more complicated than it needs to be.
The purpose of the stored procedure is to request all these fields for certain month/year combinations. The database is very highly normalized and I may have gotten a little lost in all the joins!
I tried removing @vstextid and all references to it. I received error the the subquery returns more than one value.
So I'll have to look more closely at the links in the subqueries.
Again thanks for all the help.
April 28, 2010 at 12:35 am
- you also need to take care of the inner join that you use in combination with a left joined object !!
- for the moment you are using row level nested subqueries. You should also test if a regular left join might perform better for this query.
Us a left join for this to simulate the row level subquery, because that will provide a NULL if the query doesn't return a value.
What's with all the parantheses in the from part ??
Remove them, and you'll get less confused.
CREATE PROCEDURE [dbo].[spvisitstuff]
@refdate datetime
as
begin
declare @startdate datetime, @enddate datetime
Select @startdate = dateadd(mm, datediff(mm,0,@refdate),0) -- gives you day 1 of the month
select @enddate = dateadd(mm,1,@startdate) -- day 1 of next month
SELECT TPM300_PAT_VISIT.vst_ext_id
, TSM180_MST_COD_DTL.cod_dtl_ext_id
, TSM180_MST_COD_DTL_2.cod_dtl_ext_id
, TPM300_PAT_VISIT.dschrg_ts
, TPM300_PAT_VISIT.med_rec_no
, TPM300_PAT_VISIT.adm_ts
, TPM300_PAT_VISIT.drg_cd
, TPM318_VISIT_DIAGNOSIS.ICD9_diag_ty
, TSM910_ICD9_REF.icd9_code
, TPB200_BILLING_INVOICE.ttl_charges_billed_at
, TPB200_BILLING_INVOICE.billing_date
, TSM180_MST_COD_DTL_1.cod_dtl_ext_id
, TSM180_MST_COD_DTL_3.cod_dtl_ext_id
, TSM180_MST_COD_DTL_4.cod_dtl_ext_id
, TSM180_MST_COD_DTL_5.cod_dtl_ext_id
, TSM180_MST_COD_DTL_6.cod_dtl_ext_id
, VMGH003_PATIENT.FNAME
, VMGH003_PATIENT.LNAME
, VMGH003_PATIENT.MI
, VMGH003_PATIENT.STREET
, VMGH003_PATIENT.CITY
, VMGH003_PATIENT.STATE
, VMGH003_PATIENT.ZIPCODE
, VMGH003_PATIENT.PHONE
, TSM180_MST_COD_DTL_7.cod_dtl_ext_id
, VMGH003_PATIENT.DOB
, VMGH003_PATIENT.SEX
, TSM180_MST_COD_DTL_8.cod_dtl_ext_id
, TSM040_PERSON_HDR.bth_ts
, TSM180_MST_COD_DTL_9.cod_dtl_ext_id
, VMGH003_PATIENT.SSN
, payors.payor_primary
, payours.payor_secondary
FROM dbo.TPM300_PAT_VISIT TPM300_PAT_VISIT
INNER JOIN dbo.TSM180_MST_COD_DTL TSM180_MST_COD_DTL WITH ( NOLOCK )
ON TPM300_PAT_VISIT.pat_cat_cd = TSM180_MST_COD_DTL.cod_dtl_int_id
INNER JOIN dbo.TSM040_PERSON_HDR TSM040_PERSON_HDR WITH ( NOLOCK )
ON TPM300_PAT_VISIT.psn_int_id = TSM040_PERSON_HDR.psn_int_id
INNER JOIN dbo.TSM180_MST_COD_DTL TSM180_MST_COD_DTL_1 WITH ( NOLOCK )
ON TPM300_PAT_VISIT.adm_ty = TSM180_MST_COD_DTL_1.cod_dtl_int_id
INNER JOIN dbo.TSM180_MST_COD_DTL TSM180_MST_COD_DTL_3 WITH ( NOLOCK )
ON TPM300_PAT_VISIT.adm_src_cd = TSM180_MST_COD_DTL_3.cod_dtl_int_id
INNER JOIN dbo.TSM180_MST_COD_DTL TSM180_MST_COD_DTL_5 WITH ( NOLOCK )
ON TPM300_PAT_VISIT.dschg_sta_cd = TSM180_MST_COD_DTL_5.cod_dtl_int_id
INNER JOIN dbo.TSM180_MST_COD_DTL TSM180_MST_COD_DTL_6 WITH ( NOLOCK )
ON TPM300_PAT_VISIT.fin_cls_cd = TSM180_MST_COD_DTL_6.cod_dtl_int_id
LEFT OUTER JOIN dbo.VMGH003_PATIENT VMGH003_PATIENT WITH ( NOLOCK )
ON TPM300_PAT_VISIT.vst_int_id = VMGH003_PATIENT.vst_int_id
LEFT OUTER JOIN dbo.TSM180_MST_COD_DTL TSM180_MST_COD_DTL_2 WITH ( NOLOCK )
ON TPM300_PAT_VISIT.pat_ty = TSM180_MST_COD_DTL_2.cod_dtl_int_id
LEFT OUTER JOIN dbo.TPM318_VISIT_DIAGNOSIS TPM318_VISIT_DIAGNOSIS WITH ( NOLOCK )
ON TPM300_PAT_VISIT.vst_int_id = TPM318_VISIT_DIAGNOSIS.vst_int_id
LEFT OUTER JOIN dbo.TPB200_BILLING_INVOICE TPB200_BILLING_INVOICE WITH ( NOLOCK )
ON TPM300_PAT_VISIT.vst_int_id = TPB200_BILLING_INVOICE.vst_int_id
LEFT OUTER JOIN dbo.TSM180_MST_COD_DTL TSM180_MST_COD_DTL_4 WITH ( NOLOCK )
ON TPM300_PAT_VISIT.adm_src_cd = TSM180_MST_COD_DTL_4.cod_dtl_int_id
LEFT OUTER JOIN dbo.TSM180_MST_COD_DTL TSM180_MST_COD_DTL_9 WITH ( NOLOCK )
ON TPM300_PAT_VISIT.cny_cd = TSM180_MST_COD_DTL_9.cod_dtl_int_id
INNER JOIN dbo.TSM910_ICD9_REF TSM910_ICD9_REF WITH ( NOLOCK )
ON TPM318_VISIT_DIAGNOSIS.icd9_int_id = TSM910_ICD9_REF.icd9_int_id
LEFT OUTER JOIN dbo.TSM180_MST_COD_DTL TSM180_MST_COD_DTL_7 WITH ( NOLOCK )
ON TSM040_PERSON_HDR.mry_sta_cd = TSM180_MST_COD_DTL_7.cod_dtl_int_id
LEFT OUTER JOIN dbo.TSM180_MST_COD_DTL TSM180_MST_COD_DTL_8 WITH ( NOLOCK )
ON TSM040_PERSON_HDR.rac_cd = TSM180_MST_COD_DTL_8.cod_dtl_int_id
LEFT JOIN (
SELECT TPM300_PAT_VISIT.vst_ext_id
, max(case TPM311_VISIT_PAYOR.pyr_seq_no
when 4981 then TPM700_PAYOR_PLAN.plan_ext_id
else NULL
end) as payor_primary
, max(case TPM311_VISIT_PAYOR.pyr_seq_no
when 4978 then TPM700_PAYOR_PLAN.plan_ext_id
else NULL
end) as payor_secondary
FROM dbo.TPM300_PAT_VISIT TPM300_PAT_VISIT
INNER JOIN dbo.TPM311_VISIT_PAYOR TPM311_VISIT_PAYOR ( NOLOCK )
ON TPM300_PAT_VISIT.vst_int_id = TPM311_VISIT_PAYOR.vst_int_id
INNER JOIN dbo.TPM700_PAYOR_PLAN TPM700_PAYOR_PLAN ( NOLOCK )
ON TPM311_VISIT_PAYOR.plan_int_id = TPM700_PAYOR_PLAN.plan_int_id
WHERE TPM311_VISIT_PAYOR.pyr_seq_no in ( 4981, 4978 )
-- and TPM300_PAT_VISIT.vst_ext_id = @vstextid
group by TPM300_PAT_VISIT.vst_ext_id
) as payors
on payors.vst_ext_id = TPM300_PAT_VISIT.vst_ext_id
WHERE TSM180_MST_COD_DTL.cod_dtl_ext_id not in ( 'CXL', 'PRE' )
AND TSM180_MST_COD_DTL_2.cod_dtl_ext_id not in ( 'C', 'L' )
and TPM300_PAT_VISIT.vst_ext_id = @vstextid
and TPM300_PAT_VISIT.dschrg_ts >= @startdate
and TPM300_PAT_VISIT.dschrg_ts < @enddate
Off course you still need to figure out how you'll get the value ro @vstextd.
My guess (in the current sproc) is :
or remove it
or have it provided as an input parameter for your sproc
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 28, 2010 at 7:11 am
I'm trying to run the stored procedure as you have suggested, without the subqueries and I have what I hope is not a dumb question. When executing the command to run the sp, what type of date would you put in? Eg:
exec spvisitstuff 04/01/2008
exec spvisitstuff 20080401 etc? These examples are not working.
I do still need to figure out how to get the values to @vstextid. This will not be used for just one id, they need all for the month.
And thanks again for the help!
April 28, 2010 at 7:17 am
Ok, I figured out the date,
exec spvisitstuff '2008-04-01' works!
Any help with building the loop through the id's is greatly appreciated though:)
Thanks!
April 28, 2010 at 8:32 am
Do you need info of some ids or of all ids ?
If all, just remove it from the where clause.
If some: can you predefine them ?
Avoid loops, think sets !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 29, 2010 at 9:59 am
I removed reference to the vstextid and it works good. Thanks very much for all the help.
April 30, 2010 at 11:36 am
I was wondering how the max works in the max(case... portion of your example?
Also, what did you mean when you referred to this:
- you also need to take care of the inner join that you use in combination with a left joined object !!
I have got got this working pretty well. I added another part that's giving me some duplicates, but that's another whole question!
Again, thanks for all the help.
April 30, 2010 at 2:14 pm
Denise McMillan (4/30/2010)
I was wondering how the max works in the max(case... portion of your example?Also, what did you mean when you referred to this:
- you also need to take care of the inner join that you use in combination with a left joined object !!
I have got got this working pretty well. I added another part that's giving me some duplicates, but that's another whole question!
Again, thanks for all the help.
I use the max functions to get to a single result row, for every TPM300_PAT_VISIT.vst_ext_id.
Each case handles a single TPM311_VISIT_PAYOR.pyr_seq_no
( 4981, 4978 )
After rereading the query I see I have misinterpreted the join condition.
Disregard that remark. Sorry for that.:blush:
There is nothing wrong with that inner join, because its on-clause only refers to columns of another inner joined object.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply