User Defined Function using Output

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 2005 Forums

Transact-SQL (2005)

User Defined Function using Output

Author

Topic

CindyT
Starting Member

3 Posts

Posted - 2011-10-19 : 20:15:21

I am in need of some help with a user defined function. The end result that I am looking for is to have all rows for a given table returned based on calculations that are being done in the first user defined function.

Query calling UDF
declare @patient_total NUMERIC(12,2)
declare @ins_total NUMERIC(12,2)
exec dbo.usp_calculate_amount_owing
@riddebtor_in = '0000000173:00019:00000',
@patient_total_out =@patient_total output,
@insurance_total_out = @ins_total output
select thePatTotal = @patient_total, theInsTotal = @ins_total
GO

UDF being called is passing the @patient_total_out & @insurance_total_out, this udf has been created to calculate based on one record being passed so I am only getting one record back. I've hardcoded recordnum in the "@riddebtor_in" to the udf in order to receive a record for the result

Shown below is the user defined function, which is fairly complex

---------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_Calculate_Amount_Owing]
@ridDebtor_in VARCHAR(25),
@patient_total_out NUMERIC(12,2) = -9999999.9999 OUTPUT,
@insurance_total_out NUMERIC(12,2) = -9999999.9999 OUTPUT
/***************************************************************************************************************************************
Name: usp_Calculate_Amount_Owing
Description: Calculates patient balance
Parameters Accepted:
@ridDebtor = code used to link to ridDebtor field in the trans4 table - patient specific key
Parameters Returned:
@insurance_total = total amount deferrred to insurance
@patient_total = current patient balance
Author: GMD - GSD
Date: 2005-08-06
Version: 1.0

Modification History

Date Who Version Description
2005-08-16 WW For Gsdl Knowledgeg R3
2005-08-18 WW SK R4
2010-11-10 JCD 6.0.15 Performance enhancements on large tables
---------------------------------------------------------------------------------------------------------

********************************************************************************************************************************************/
AS
BEGIN
DECLARE @v_trans_recnum VARCHAR(25);
DECLARE @v_trans_amount NUMERIC(18,4);
DECLARE @v_trans_invnum VARCHAR(11);
DECLARE @v_trans_type NUMERIC(10,0);
DECLARE @v_trans_othertype NUMERIC(10,0);
DECLARE @v_trans_response NUMERIC(10,0);
DECLARE @v_insclaim_uidclaim VARCHAR(25);
DECLARE @v_ptrns_expected NUMERIC(18,4);

DECLARE @v_total_insurance_expected NUMERIC(18,4);
DECLARE @v_total_insurance_paid NUMERIC(18,4);
DECLARE @v_grand_total NUMERIC(18,4);

SET @v_total_insurance_expected = 0;
SET @v_total_insurance_paid = 0;
SET @v_grand_total = 0;

DECLARE c_trans CURSOR FOR SELECT recordnum, cuamount, invnum, ltrnstype, lothertype, wresponse
FROM trans4
WHERE deleted = 0
AND ridpatient = @ridDebtor_in
AND NOT CAST(ltrnstype AS BIGINT) & 536870912 = 536870912 -- ignore non-financial transactions
AND NOT lothertype = 65557 -- ignore pay plan invoices
ORDER BY recordnum
FOR READ ONLY;

OPEN c_trans;

FETCH NEXT FROM c_trans INTO @v_trans_recnum, @v_trans_amount, @v_trans_invnum, @v_trans_type, @v_trans_othertype, @v_trans_response;

WHILE @@FETCH_STATUS = 0
BEGIN
-- Total Expected From Insurance
IF NOT ((@v_trans_type IN (86020, 65539) AND @v_trans_othertype = 65572) OR -- ignore insurance payments or adjustments
(@v_trans_type = 65537 AND CAST(@v_trans_response AS BIGINT) & 1024 = 1024)) -- ignore resubmitted invoices
BEGIN
DECLARE @v_total_ptrns_expected NUMERIC(18,4);
SET @v_total_ptrns_expected = 0;

DECLARE c_insclaim CURSOR FOR SELECT uidclaimnumber
FROM insclaim
WHERE deleted = 0
AND invnum = @v_trans_invnum
AND NOT wstatus = 7 -- ignore voided claims
ORDER BY uidclaimnumber
FOR READ ONLY;

OPEN c_insclaim;

FETCH NEXT FROM c_insclaim INTO @v_insclaim_uidclaim;

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE c_ptrns CURSOR FOR SELECT cuexpected
FROM ptrns
WHERE deleted = 0
AND uidclaimnum = @v_insclaim_uidclaim
AND ltrnsnum = @v_trans_recnum
FOR READ ONLY;

OPEN c_ptrns;

FETCH NEXT FROM c_ptrns INTO @v_ptrns_expected;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @v_total_ptrns_expected = @v_total_ptrns_expected + @v_ptrns_expected;

FETCH NEXT FROM c_ptrns INTO @v_ptrns_expected;
END; -- c_ptrns loop

CLOSE c_ptrns;
DEALLOCATE c_ptrns;

FETCH NEXT FROM c_insclaim INTO @v_insclaim_uidclaim;
END; -- c_insclaim loop

CLOSE c_insclaim;
DEALLOCATE c_insclaim;

IF @v_total_ptrns_expected > 0
BEGIN
IF @v_trans_amount < @v_total_ptrns_expected
SET @v_total_insurance_expected = @v_total_insurance_expected + @v_trans_amount;
ELSE
SET @v_total_insurance_expected = @v_total_insurance_expected + @v_total_ptrns_expected;
END;
END;

-- Total Paid By Insurance
IF (@v_trans_type IN (86020, 65539) AND @v_trans_othertype = 65572) -- include only insurance payments and adjustments
SET @v_total_insurance_paid = @v_total_insurance_paid + @v_trans_amount;

-- Grand Total
SET @v_grand_total = @v_grand_total + @v_trans_amount;

FETCH NEXT FROM c_trans INTO @v_trans_recnum, @v_trans_amount, @v_trans_invnum, @v_trans_type, @v_trans_othertype, @v_trans_response;
END; -- c_trans loop

CLOSE c_trans;
DEALLOCATE c_trans;

SET @insurance_total_out = ISNULL(@v_total_insurance_expected, 0) + ISNULL(@v_total_insurance_paid, 0);
SET @patient_total_out = ISNULL(@v_grand_total, 0) - (ISNULL(@v_total_insurance_expected, 0) + ISNULL(@v_total_insurance_paid, 0));
END;

CindyT
Starting Member

3 Posts

Posted - 2011-10-19 : 20:38:24

To simplify, forget the entailed udf and let me simplify

I am passing a value (patient id) and adding amts in various records for a given patient id and the udf then returns the amts in 2 fields, insurance and patient

Once I get this information I now need to get a record for each patient in order to create a report with given amts for each patient

The udf is complex due to how amt are computed to come up with the balances......

So with the complex udf put aside, does anyone know how I can get all rows returned???

Any help is greatly appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-20 : 04:39:45

its not even a udf. what you've on top is procedure. you can very easily add logic inside that to iterate for each patientids in a given table and then return two amounts for them in select resultset

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

CindyT
Starting Member

3 Posts

Posted - 2011-10-20 : 08:59:30

Sorry about the confusion on the function and procedure, this is something I am not familiar with and trying to find a way to get the results I need.

Can you elaborate on how I can add the logic?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-20 : 09:20:34

it should be like

INSERT into Yourtable
SELECT <your current variable values>

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources