Help adding Parameters to Stored Procedure

[mchambers]

Hello,

I am having some trouble adding 3 more Parameters to an already written stored procedure. I have added the parameters at the top already. They are @protocol, @staff and @service. I have tried to fit them in the script but it is either not pulling the data or pulling data I am not calling. I am very new to stored procedures, having only written basic ones. If anyone can assist me I would greatly appreciate it. Below is script:

USE [rpt]
GO
/****** Object:  StoredProcedure [dbo].[psp_19501_extract_financial_data2]    Script Date: 04/20/2013 07:04:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

alter procedure [dbo].[psp_19501_extract_financial_data2] ( @startdt datetime,@enddt datetime, @incstand char(3), @coverage varchar(400), @protocol char(10), @staff char(10), @service char(10) )
as


/************************************************************************************************************************
*                                                                    
*	Created to extract data to display in a crystal report that will show 
*	show financial data for patient accounts per "Services No Client Name Report Specifications"
*           
*
************************************************************************************************************************/

set nocount on
set @coverage=UPPER(@coverage)

---first pass - extract the primary Payer for that is the first physical payer in the coverage list
select (select report_header from Institution  with (nolock))as 'facility',bt.billing_trans_no,bt.patient_id,bt.episode_id,bt.hosp_status_code,bt.billing_proc_code,bt.proc_duration,bt.proc_duration AS 'ITDA',bt.proc_duration AS 'count_ITDA',bt.proc_duration as 'count_proc_duration',bt.duration_type,bt.billing_amt,bt.payor_disallowance,bt.payor_payment,btb.balance_amount, bt.copay_priority, bt.coverage_plan_id,bt.proc_chron, bt.billing_proc_desc, bt.clinic_id,bt.program_id,bt.service_id,bt.protocol_id,bt.clinician_id, s.fname,s.lname,rtrim(s.lname)+', '+s.fname as 'full_name',bt.patient_copay, pct.clinical_transaction_no, '1' as 'extract_type', space(50) as 'patient_name',0000000.00 AS 'payment_amt'
into #temp1 from Billing_Transaction bt with (nolock)
	left join Billing_Transaction_Balance btb  with (nolock)on btb.billing_trans_no=bt.billing_trans_no
	join Staff s  with (nolock) on s.staff_id=bt.clinician_id
	join Patient_Clin_Tran pct  with (nolock) on pct.clinical_transaction_no=bt.clinical_transaction_no
	where pct.is_error<>'Y'
	and (@coverage like '%All%' or @coverage like '%'+rtrim(bt.coverage_plan_id)+'%') 
	and bt.clin_trans_status='CO'
	and convert(varchar(8),bt.proc_chron,112)>=convert(varchar(8),@startdt,112) 
	and convert(varchar(8),bt.proc_chron,112)<=convert(varchar(8),@enddt,112) 
	--and bt.copay_priority<=7
	--going to get the group of coverages that are first physically here and exclude those that need top be excluded)
	and bt.coverage_plan_id<>'STANDARD'
	and (bt.copay_priority=1 and bt.billing_amt>0 and (bt.payor_disallowance>0 or bt.payor_payment>0))
	

	
---second pass - get the primary payer that is not standard but not the first physical coverage in the coverage list
insert into #temp1
select (select report_header from Institution  with (nolock))as 'facility',bt.billing_trans_no,bt.patient_id,bt.episode_id,bt.hosp_status_code,bt.billing_proc_code,bt.proc_duration,bt.proc_duration AS 'ITDA',bt.proc_duration AS 'count_ITDA',bt.proc_duration as 'count_proc_duration',bt.duration_type,bt.billing_amt,bt.payor_disallowance,bt.payor_payment,btb.balance_amount, bt.copay_priority, bt.coverage_plan_id,bt.proc_chron, bt.billing_proc_desc, bt.clinic_id,bt.program_id,bt.service_id,bt.protocol_id,bt.clinician_id, s.fname,s.lname,rtrim(s.lname)+', '+s.fname as 'full_name',bt.patient_copay, pct.clinical_transaction_no, '2' as 'extract_type', space(50) as 'patient_name',0000000.00 AS 'payment_amt'
from Billing_Transaction  bt  with (nolock)
	left join Billing_Transaction_Balance btb  with (nolock) on btb.billing_trans_no=bt.billing_trans_no
	join Staff s  with (nolock) on s.staff_id=bt.clinician_id
	join Patient_Clin_Tran  pct  with (nolock) on pct.clinical_transaction_no=bt.clinical_transaction_no
	where pct.is_error<>'Y'
	and (@coverage like '%All%' or @coverage like '%'+rtrim(bt.coverage_plan_id)+'%')  
	and bt.clin_trans_status='CO'
	and convert(varchar(8),bt.proc_chron,112)>=convert(varchar(8),@startdt,112) 
	and convert(varchar(8),bt.proc_chron,112)<=convert(varchar(8),@enddt,112) 
	and bt.copay_priority<=7
	--going to get the group of coverages that are not first physically here but are still the primary payer)
	and bt.coverage_plan_id<>'STANDARD'
	--at this point only coverages with copay-priority 1 are in #temp1
	and bt.copay_priority>1 
	and bt.billing_amt>0 
	and (bt.payor_disallowance>0 or bt.payor_payment>0)  
	and (select count(t1u.coverage_plan_id) 
								from #temp1 t1u where t1u.clinical_transaction_no=bt.clinical_transaction_no )=0

								
---third pass- standard coverage for those patients in the file already with other Primary Payers - set count_proc_duration to 0 wont be included in the count
insert into #temp1
select (select report_header from Institution  with (nolock))as 'facility',bt.billing_trans_no,bt.patient_id,bt.episode_id,bt.hosp_status_code,bt.billing_proc_code,bt.proc_duration,bt.proc_duration AS 'ITDA',bt.proc_duration AS 'count_ITDA',0 as 'count_proc_duration',bt.duration_type,bt.billing_amt,bt.payor_disallowance,bt.payor_payment,btb.balance_amount, bt.copay_priority, bt.coverage_plan_id,bt.proc_chron, bt.billing_proc_desc, bt.clinic_id,bt.program_id,bt.service_id,bt.protocol_id,bt.clinician_id, s.fname,s.lname,rtrim(s.lname)+', '+s.fname as 'full_name',bt.patient_copay, pct.clinical_transaction_no, '3' as 'extract_type', space(50) as 'patient_name',0000000.00 AS 'payment_amt'
from Billing_Transaction  bt  with (nolock)
	left join Billing_Transaction_Balance btb  with (nolock) on btb.billing_trans_no=bt.billing_trans_no
	join Staff s  with (nolock) on s.staff_id=bt.clinician_id
	join Patient_Clin_Tran pct  with (nolock) on pct.clinical_transaction_no=bt.clinical_transaction_no
	where pct.is_error<>'Y'
	and bt.clin_trans_status='CO'
	and convert(varchar(8),bt.proc_chron,112)>=convert(varchar(8),@startdt,112) 
	and convert(varchar(8),bt.proc_chron,112)<=convert(varchar(8),@enddt,112) 
	and bt.copay_priority<=7
	--going to get the group of coverages that are standard for the clinical transactions that are already in the file.
	and bt.coverage_plan_id='STANDARD'
	and (@coverage ='STANDARD_ONLY' or (select count(t1u.coverage_plan_id) 
								from #temp1 t1u where t1u.clinical_transaction_no=bt.clinical_transaction_no )>0)

if @coverage ='STANDARD_ONLY'
	begin
		update #temp1
		set count_proc_duration=proc_duration
	end

	
---fourth pass - get the Patients where the Patient (Standard) is the Primary Payer (Only Payer) and rename coverage to identify
insert into #temp1
select (select report_header from Institution  with (nolock))as 'facility',bt.billing_trans_no,bt.patient_id,bt.episode_id,bt.hosp_status_code,bt.billing_proc_code,bt.proc_duration,bt.proc_duration AS 'ITDA',bt.proc_duration AS 'count_ITDA',bt.proc_duration as 'count_proc_duration',bt.duration_type,bt.billing_amt,bt.payor_disallowance,bt.payor_payment,btb.balance_amount, bt.copay_priority, 'PATIENT-PR' as 'coverage_plan_id',bt.proc_chron, bt.billing_proc_desc, bt.clinic_id,bt.program_id,bt.service_id,bt.protocol_id,bt.clinician_id, s.fname,s.lname,rtrim(s.lname)+', '+s.fname as 'full_name',bt.patient_copay, pct.clinical_transaction_no, '4' as 'extract_type', space(50) as 'patient_name',0000000.00 AS 'payment_amt'
from Billing_Transaction bt  with (nolock)
	left join Billing_Transaction_Balance btb  with (nolock) on btb.billing_trans_no=bt.billing_trans_no
	join Staff s  with (nolock) on s.staff_id=bt.clinician_id
	join Patient_Clin_Tran pct  with (nolock) on pct.clinical_transaction_no=bt.clinical_transaction_no
	where pct.is_error<>'Y'
	and @coverage <>'STANDARD_ONLY'
	and (@coverage like '%ALL%' or @coverage like '%PATIENT-PR%' ) 
	and bt.clin_trans_status='CO'
	and convert(varchar(8),bt.proc_chron,112)>=convert(varchar(8),@startdt,112) 
	and convert(varchar(8),bt.proc_chron,112)<=convert(varchar(8),@enddt,112) 
	and bt.copay_priority<=7
	--going to get the group of coverages that the standard is the primary payer.
	and bt.coverage_plan_id='STANDARD'
	and bt.billing_amt>0 
	and (bt.payor_disallowance>0 or bt.payor_payment>0)
	and (select top 1 bt2.coverage_plan_id from Billing_Transaction bt2  with (nolock) 
			where bt2.clinical_transaction_no=bt.clinical_transaction_no
			and bt2.billing_amt>0 
			and (bt2.payor_disallowance>0 or bt2.payor_payment>0 or bt2.coverage_plan_id='STANDARD') 
			order by bt.copay_priority)='STANDARD'
			


update #temp1
set balance_amount=0 where balance_amount is null

--alter the duration to match the expected logic which is:
--For the column  ITDA
-----it will contain a 1 if the duration_type is (IT or DA)
-----it will be empty if the duration_type is NOT (IT or DA)
--For the column Dur
-----It will contain the duration value from the clinical transaction if the duration_type is not IT or DA 
update #temp1
set ITDA=1, proc_duration=0,count_proc_duration=0,count_ITDA=1 where duration_type='IT' or duration_type='DA'
update #temp1
set ITDA=0, count_ITDA=0 where duration_type<>'IT' and duration_type<>'DA'
update #temp1
set count_proc_duration=0,count_ITDA=0 where coverage_plan_id='STANDARD'
-------
alter TABLE #temp1 ALTER COLUMN payment_amt money
update t1
set t1.payment_amt= (select sum(amount) from billing_ledger bl with (nolock) where bl.billing_trans_no=t1.billing_trans_no and bl.clinical_transaction_no=t1.clinical_transaction_no and subtype='PA') from #temp1 t1


if @incstand<>'Yes'
	begin
		delete from #temp1 where coverage_plan_id='STANDARD'
	end

update t1
	set patient_name=(select rtrim(p.lname)+', '+p.fname from Patient p where p.patient_id=t1.patient_id and p.episode_id=t1.episode_id) from #temp1 t1

update #temp1
set duration_type='' where duration_type='MI'

select @coverage as 'Coverage_Parm',@startdt as 'start',@enddt as 'enddt',* from #temp1 t1

drop table #temp1

grant exec on dbo.psp_19501_extract_financial_data to secure

 

[mchambers]

I forgot to add that I am trying to call @service = billing_proc_code, @protocol = protocol_id and @staff = billing_clinician_id. Thanks

 

[djj55]

1) Does the values get to the stored procedure? - I would assume yes
2) What do you mean by call?
- something like IF @service = Billing_Proc_code - if so look at IF @converage = 'STANDARD_ONLY' for an example
3) Will these prarameters always be provided?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[mchambers]

The was originally written to work with a crystal report and now I have to switch it over to work with Reporting Services. The date and coverage parameters work fine but I cannot get the report to accept the other 3 parameters I need to add. I tried to add them through reporting services but that did not work either.

I guess I used call wrong. and yes someone would always enter in those parameters when they run the report. They would be able to use select all or pick any choice they want to see in the report. So the output should be something like this

parameters choice sdate = 07/01/2010, edate = 07/31/2010, coverage = Medicaid, service = Bedday, protocol = 111, staff = 001

patient service date coverage staff
002 Bedday 07/21/2010 Medicaid 001


Thanks for helping me with this, really appreciate it!

 

[SQLBill]

On a different issue...this needs to be fixed.

drop table #temp1

grant exec on dbo.psp_19501_extract_financial_data to secure

That grant is part of your stored procedure, so everytime the stored procedure gets called it has to run the grant statement. You should put a GO between the end of the stored procedure (in this case the DROP TABLE #temp1) and the grant statement.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLBill]

There's a lot of code there, so what I did was to copy it into Notepad. Then, since your issue is with @protocol, @staff, and @service...I did a search to see where you use those three parameters. Surprise....you don't use them anywhere. That's the problem. You are supplying the values, but you never use them in your query.


For example:


CREATE PROCEDURE finduser @lname VARCHAR(100)
AS
SELECT *
FROM employee
WHERE Last_name = @lname
GO

EXEC finduser @lname = 'Smith'
GO

That would find all the employees who have a last name of Smith. But if you do this, it will return all the employees even though you only ask for @lname = 'smith'. The script in the stored procedure never uses the parameter.

CREATE PROCEDURE finduser @lname VARCHAR(100)
AS
SELECT *
FROM employee
GO

EXEC finduser @lname = 'Smith'
GO


-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[mchambers]

Thanks sqlbill, I actually did have the parameters in the code but removed them because they did not work. I left them in the top section to show which parameters I was trying to use. I placed them in each section of passes but they did not give me the results from each parameter. The only parameters that worked were the data and coverage parameters that were already in the code.

 

[SQLBill]

Here's the thing...because you removed the parameters from the code, we can't tell how you were trying to use them. So we can't tell if you have a typo, syntax error, or a logical error. Here's what really needs to happen. You need to put the parameters into your code where and how you think they belong to get the data you need. Then execute the stored procedure. If errors are returned, we need to know what they are. If the data returned isn't what you want we need to know that.

So, you need to provide us:
The actual code you are trying to run.
Any error messages.
A sample of the data returned (if there is data returned).
What you expected to see.

The sample data should be as realistic as possible, but we do understand that some data might be confidential. Replace that data with fake data, but understand that the data we are looking at is what we will use to troubleshoot your issue.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[djj55]

If the issue is with the Crystal Reports passing the parameter, you might try asking in that forum.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[IanWaterman]

When you use an SP with Crystal it will automatically detect the SP parameters and allow users to enter values without any design intervention. Reporting services is not so helpful. YOu must create them first in RS and then use them in the SQL query

Exec psp_19501_extract_financial_data2 @startdt, @enddt, @incstand, @coverage, @protocol, @staff, @service

Ian

 

[philhege]

Create the parameters in RS, map them to the SP parameters in the dataset , then use them in the SQL query.

-----------
With business clients like mine, you'd be better off herding cats.