Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Stored Procedure won't recognize Parameters

Author  Topic 

bconner
Starting Member

48 Posts
Posted - 2012-10-15 : 17:24:19
[code]
CREATE PROCEDURE [dbo].[sp_Charges_Equal_Payments]

AS

DECLARE @BEGIN_POST_PERIOD INT
DECLARE @END_POST_PERIOD INT



SELECT * INTO #Charges

FROM

(SELECT *
FROM Transactions.dbo.Transactions
WHERE [PAY_CODE_NUMBER] = 99
AND [ORIGINAL_FSC] NOT IN (1,150,155,151)
AND [CHARGE_AMOUNT] <>0
AND [POSTING_PERIOD] BETWEEN @BEGIN_POST_PERIOD AND @END_POST_PERIOD) T1



SELECT * INTO #Payments

FROM

(SELECT
T1.[INVOICE_NUMBER]
,T1.[POSTING_PERIOD] AS PAYMENT_POSTING_PERIOD
,T1.[PAY_CODE_NUMBER]
,T1.[LINE_ITEM]
,T1.[PAYMENT_AMOUNT]
FROM Transactions.dbo.Transactions T1
JOIN Dictionaries.dbo.Paycode T2 ON T1.PAY_CODE_NUMBER = T2.Paycode
JOIN #Charges T3 ON T1.INVOICE_NUMBER = T3.INVOICE_NUMBER
WHERE T1.[PAYMENT_AMOUNT] <>0
AND T2.[Reporting Category 2] <> 'SELF PAY') T2


SELECT [Group_Number]
,[Division_Name]
,[Billing_Area_Id]
,[Billing_Area_Name]
,[Location_Name]
,[Location_Mnemonic]
,[Location_RptCat3]
,T1.[INVOICE_NUMBER]
,[PATIENT_ID]
,[Pt_MRN]
,[Pt_Last_Name]
,[Pt_First_Name]
,[Pt_DOB]
,[Pt_Addr1]
,[Pt_Addr2]
,[Pt_City_State]
,[Pt_Zip]
,[ORIGINAL_FSC]
,[FSC_AT_Transaction]
,T1.[PAY_CODE_NUMBER]
,T1.[LINE_ITEM]
,[Procedure_Code]
,[UNITS_SERVICE_ANALY_MOD]
,[POSTING_DATE]
,[POSTING_PERIOD]
,PAYMENT_POSTING_PERIOD
,[CHARGE_AMOUNT]
,T2.[PAYMENT_AMOUNT]
,[ADJUSTMENT_AMOUNT]
,[CREDIT_AMOUNT]
,[DEBIT_AMOUNT]
,[COPAY_AMOUNT]
,[APPROVED_AMOUNT]
,[ALLOWED_AMOUNT]
,[DEDUCTIBLE_AMOUNT]
,[Patient_Responsibility]
,[REJ_1]
,[REJ_2]
,[REJ_3]
,[REJ_4]
,[SERVICE_DATE]
,[Inv_DOS]
,[Ref_Physician_Name]
,[Ref_Location_Id]
,[Ref_Name]
,[Ref_Number]
,[Ref_Address_Line_1]
,[Ref_Address_Line_2]
,[Ref_City_State]
,[Ref_Zip_Code]
,[Reading_LOC_Id]
,[Reading_Loc_Name]
,[Reading_Loc_Address]
,[Reading_Loc_City_State]
,[Performing_Loc_Id]
,[Performing_Loc_Name]
,[Performing_Loc_Address]
,[Performing_Loc_City_State]
,[Provider_id]
,[Provider_Name]
,[Provider_Number]
,[Provider_Category]
,[MODIFIER_1]
,[MODIFIER_2]
,[MODIFIER_3]
,[Diagnosis_Pointer]
,[DIAGNOSIS_1]
,[DIAGNOSIS_2]
,[DIAGNOSIS_3]
,[AP_CPC_Flag]
,[BATCH_NUMBER]
,[Initials]
,[Accession_Number]
,[CERTIFICATE_NUMBER]
,[INSURANCE_GROUP]
,[PLAN_NUMBER]
,[Ins_Comp_Payerid]
,[Ins_Comp_Name]
,[Ins_Comp_Alt_Name]
,[Ins_Comp_Address1]
,[Ins_Comp_Address2]
,[Ins_Comp_City_State]
,[Ins_Comp_Zip_Code]
,[Current_FSC]
,[Corrected_Invoice_Number]
,[FEE_SCHEDULE]
,[FEE_SCHEDULE_EFFECTIVE_DT]
,[First_Credit_Date]
,[BANK_DEPOSIT_DATE]
,[COMMENT]
,[LINES_PAID]
,[PREVIOUS_FSC_ID]
,[STATEMENT_RUN_NUMBER]
,[CorrespondingLocationOnMedicareForm]
,[CorrespondingLocationCodeForMedicaid]
,[CorrespondingLocationForHic]
,[Original_Fsc_Name]
,[Current_Fsc_Name]
,[Original_Fsc_RptCat2]
,[Current_Fsc_RptCat2]
,[Paycode Description]
,[Paycode_RptCat2]
,[Paycode_RptCat4]
,[Transaction_Level]
,[FIN_DM_LoadTime]
,[PREVIOUS_FSC_NAME]

FROM #Charges T1
JOIN
(SELECT
[INVOICE_NUMBER]
,PAYMENT_POSTING_PERIOD
,[PAY_CODE_NUMBER]
,[LINE_ITEM]
,[PAYMENT_AMOUNT]
FROM #Payments) T2 ON (T1.[INVOICE_NUMBER] = T2.[INVOICE_NUMBER] AND T1.[FSC_AT_Transaction] = T2.[PAY_CODE_NUMBER] AND T1.[LINE_ITEM] = T2.[LINE_ITEM])

WHERE [CHARGE_AMOUNT] = T2.[PAYMENT_AMOUNT]

GROUP BY
[Group_Number]
,[Division_Name]
,[Billing_Area_Id]
,[Billing_Area_Name]
,[Location_Name]
,[Location_Mnemonic]
,[Location_RptCat3]
,T1.[INVOICE_NUMBER]
,[PATIENT_ID]
,[Pt_MRN]
,[Pt_Last_Name]
,[Pt_First_Name]
,[Pt_DOB]
,[Pt_Addr1]
,[Pt_Addr2]
,[Pt_City_State]
,[Pt_Zip]
,[ORIGINAL_FSC]
,[FSC_AT_Transaction]
,T1.[PAY_CODE_NUMBER]
,T1.[LINE_ITEM]
,[Procedure_Code]
,[UNITS_SERVICE_ANALY_MOD]
,[POSTING_DATE]
,[POSTING_PERIOD]
,PAYMENT_POSTING_PERIOD
,[CHARGE_AMOUNT]
,T2.[PAYMENT_AMOUNT]
,[ADJUSTMENT_AMOUNT]
,[CREDIT_AMOUNT]
,[DEBIT_AMOUNT]
,[COPAY_AMOUNT]
,[APPROVED_AMOUNT]
,[ALLOWED_AMOUNT]
,[DEDUCTIBLE_AMOUNT]
,[Patient_Responsibility]
,[REJ_1]
,[REJ_2]
,[REJ_3]
,[REJ_4]
,[SERVICE_DATE]
,[Inv_DOS]
,[Ref_Physician_Name]
,[Ref_Location_Id]
,[Ref_Name]
,[Ref_Number]
,[Ref_Address_Line_1]
,[Ref_Address_Line_2]
,[Ref_City_State]
,[Ref_Zip_Code]
,[Reading_LOC_Id]
,[Reading_Loc_Name]
,[Reading_Loc_Address]
,[Reading_Loc_City_State]
,[Performing_Loc_Id]
,[Performing_Loc_Name]
,[Performing_Loc_Address]
,[Performing_Loc_City_State]
,[Provider_id]
,[Provider_Name]
,[Provider_Number]
,[Provider_Category]
,[MODIFIER_1]
,[MODIFIER_2]
,[MODIFIER_3]
,[Diagnosis_Pointer]
,[DIAGNOSIS_1]
,[DIAGNOSIS_2]
,[DIAGNOSIS_3]
,[AP_CPC_Flag]
,[BATCH_NUMBER]
,[Initials]
,[Accession_Number]
,[CERTIFICATE_NUMBER]
,[INSURANCE_GROUP]
,[PLAN_NUMBER]
,[Ins_Comp_Payerid]
,[Ins_Comp_Name]
,[Ins_Comp_Alt_Name]
,[Ins_Comp_Address1]
,[Ins_Comp_Address2]
,[Ins_Comp_City_State]
,[Ins_Comp_Zip_Code]
,[Current_FSC]
,[Corrected_Invoice_Number]
,[FEE_SCHEDULE]
,[FEE_SCHEDULE_EFFECTIVE_DT]
,[First_Credit_Date]
,[BANK_DEPOSIT_DATE]
,[COMMENT]
,[LINES_PAID]
,[PREVIOUS_FSC_ID]
,[STATEMENT_RUN_NUMBER]
,[CorrespondingLocationOnMedicareForm]
,[CorrespondingLocationCodeForMedicaid]
,[CorrespondingLocationForHic]
,[Original_Fsc_Name]
,[Current_Fsc_Name]
,[Original_Fsc_RptCat2]
,[Current_Fsc_RptCat2]
,[Paycode Description]
,[Paycode_RptCat2]
,[Paycode_RptCat4]
,[Transaction_Level]
,[FIN_DM_LoadTime]
,[PREVIOUS_FSC_NAME]



DROP TABLE #CHARGES
DROP TABLE #PAYMENTS
Stored Procedure won't recognize Parameters




[/code]


When I run EXEC sp_Charges_Equal_Payments 201210, 201210
I get error:

Msg 8146, Level 16, State 2, Procedure sp_Charges_Equal_Payments, Line 0
Procedure sp_Charges_Equal_Payments has no parameters and arguments were supplied.


Brian

chadmat
The Chadinator

1974 Posts
Posted - 2012-10-15 : 17:30:19
CREATE PROCEDURE [dbo].[sp_Charges_Equal_Payments]

@BEGIN_POST_PERIOD INT,
@END_POST_PERIOD INT

AS...


-Chad
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-10-15 : 22:44:19
check this article to understand more on stored procedures and how you can use parameters in them to pass and return values

http://www.sqlteam.com/article/stored-procedures-returning-data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

bconner
Starting Member

48 Posts
Posted - 2012-10-16 : 07:33:07
Thanks Chadmat that did the trick! Thank you as well visakh16 the link was informative.

Brian
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-10-16 : 09:33:47
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site