Solved

How can I test this function?

Posted on 2016-07-27
4
55 Views
Last Modified: 2016-07-30
Here's my function:

USE [PCAR_Data]
GO
/****** Object:  UserDefinedFunction [dbo].[ufn_ClientPreference]    Script Date: 07/27/2016 06:56:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
ufn_ClientPreference - returns a client's preference for a given item

DROP FUNCTION [dbo].[ufn_ClientPreference]
*/

ALTER FUNCTION [dbo].[ufn_ClientPreference]
    (
      @ParmClientID BIGINT, 
      @ParmItem VARCHAR(200) 
    )
RETURNS sql_variant
    BEGIN
    
		DECLARE @Preference sql_variant
		
		SET @Preference = (select coalesce(
							(select coalesce(cast(preferenceStr as sql_variant),cast(preference as sql_variant)) preference
								from ClientPreference with (NOLOCK), client with (NOLOCK)
								where ClientPreference.ended is null
								and client.clientid = @ParmClientID
								and ClientPreference.clientid = client.clientid
								and client.clientstatuscode = ClientPreference.clientstatuscode 
								and ClientPreference.item = @ParmItem),
							coalesce(
								(select coalesce(cast(preferenceStr as sql_variant),cast(preference as sql_variant)) preference
									from ClientPreference with (NOLOCK), client with (NOLOCK)
									where ClientPreference.ended is null
									and client.clientid = @ParmClientID
									and ClientPreference.clientid = client.clientid
									and ClientPreference.clientstatuscode is null
									and ClientPreference.item = @ParmItem),
								coalesce(
									(select coalesce(cast(preferenceStr as sql_variant),cast(preference as sql_variant)) preference
										from ClientPreference with (NOLOCK), client with (NOLOCK)
										where ClientPreference.ended is null
										and client.clientid = @ParmClientID
										and ClientPreference.clientid is null
										and client.clientstatuscode = ClientPreference.clientstatuscode
										and ClientPreference.item = @ParmItem),
									(select coalesce(cast(preferenceStr as sql_variant),cast(preference as sql_variant)) preference
										from ClientPreference with (NOLOCK)
										where ClientPreference.ended is null
										and ClientPreference.clientid is null
										and ClientPreference.clientstatuscode is null
										and ClientPreference.item = @ParmItem)										
								)
							)
						  ))			
		
		RETURN @Preference
	
	END

Open in new window


I'm fairly new to MSSQL Studio and these kinds of functions in general. Bottom line: There's something potentially flawed with this function and I have to figure out what. The first thing I want to know is how can I test this?

I see how I've got two parameters. I have some test data that includes a ClientID of 31, but frankly, I'm lost when it comes to @ParamItem. "StatementRollUp" is a value in my ClientPreference table. What's fuzzy to me is that in my SELECT statement, I have this:

cast(dbo.ufn_ClientPreference(clt.ClientID, 'StatementRollUp') as int) as StatementRollUp,

Does that not look like one of my parameters is the very thing that I'm attempting to define?

So, how do I test this and what would I use for my StatementRollUp value? Here's the way it looks in my ClientPreference table:

Client Preference table
Here's the way this function looks in the context of my SELECT statement:

 OPEN SYMMETRIC KEY PCAR_Key
            DECRYPTION BY CERTIFICATE PCAR_Certificate
            SELECT
                ac.AccountID,
                clt.ClientName,
                clt.ClientTetrisID,
                clt.ClientID,
                cast(dbo.ufn_ClientPreference(clt.ClientID, 'StatementRollUp') as int) as StatementRollUp,
                prt.PracticeID,
                prt.PracticeName,
                prt.twiliophone,
                prt.showphysician,
                pat.patientfname,
                pat.patientlname,
                pat.patientinitial,
                pat.patientdob,
                pat.patientdod,
                CONVERT(varchar(100), DecryptByKey(pat.eSSN)) as patientssn,
                coalesce(ac.mobilephone, ac.homephone, ac.workphone, null) as phonenumber,
                ac.maddress1,
                ac.maddress2,
                ac.maddress3,
                ac.mcity,
                ac.mstate,
                ac.mzip,
                dbo.ufn_StatementWorkingCharges(prt.PracticeCode,ac.practiceaccountid) as TotalCharges,
                dbo.ufn_StatementWorkingAdjustments(prt.PracticeCode,ac.practiceaccountid) * -1 as TotalAdjustments,
                dbo.ufn_StatementWorkingPayments2(prt.PracticeCode,ac.practiceaccountid) * -1 as TotalPayments,
				CASE WHEN ISNULL(pp.pmtplanamount, 0) < 
				(dbo.ufn_StatementWorkingCharges(prt.PracticeCode,ac.practiceaccountid) + 
				(dbo.ufn_StatementWorkingAdjustments(prt.PracticeCode,ac.practiceaccountid) * -1) + 
				(dbo.ufn_StatementWorkingPayments2(prt.PracticeCode,ac.practiceaccountid) * -1)) 
				THEN 
				isnull(pp.pmtplanamount, 0) 
				ELSE dbo.ufn_StatementWorkingCharges(prt.PracticeCode,ac.practiceaccountid) + 
				(dbo.ufn_StatementWorkingAdjustments(prt.PracticeCode,ac.practiceaccountid) * -1) + 
				(dbo.ufn_StatementWorkingPayments2(prt.PracticeCode,ac.practiceaccountid) * -1)
				END AS AmountDue,                
				 isnull(pp.pmtplanday,0) as  duedate,
                isnull(pp.startingplanbalance, 0) as planbalance,
                ac.PracticeAccountID,
                clt.lockboxAddress1,
                clt.lockboxAddress2,
                clt.lockboxCity,
                clt.lockboxState,
                clt.lockboxZip,"
            .( $statementData ? "
                st.templateid,
                st.statementid,
                null as lastclaimdate,
                /* Temporary option to disinguish if the account has an encounter in placementgroup 1353 for the URMC lockbox move */
                (select top 1 id from chargegroup cg where cg.accountid = ac.accountid and placementgroupid = 1353) as [1353]

            from Statement st with (nolock)
                Join Account ac with (nolock) on st.Accountid = ac.AccountID
            " : "
                '$templateid' as templateid,
                '' as statementid,
                null as lastclaimdate,
                /* Temporary option to disinguish if the account has an encounter in placementgroup 1353 for the URMC lockbox move */
                (select top 1 id from chargegroup cg where cg.accountid = ac.accountid and placementgroupid = 1353) as [1353]

            from Account ac with (nolock)
            " )."
                join Patient pat with (nolock) on pat.AccountID = ac.AccountID
                join Practice prt with (nolock) on ac.PracticeID = prt.PracticeID
                join Client clt with (nolock) on prt.ClientID = clt.ClientID
                left join
                (select pmtplanamount, pmtplanday, primarypatientid, startingplanbalance from PaymentPlan
                  where planendeddate is null
                  and iscurrentplan = 1) pp
                  on pp.primarypatientid = pat.patientid
            $where

Open in new window


There you have it! How do I test this function?
0
Comment
Question by:brucegust
  • 2
  • 2
4 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41731161
That function looks funky in he extreme I'm afraid

You pass 2 parameters (int and varchar) into that function
there there are several queries returning either int or varchar values cast into sql_variant
then you use cast( that-function-result int)
wow, recipe for a nightmare it seems to me, way too many type conversions
and probably way to many subqueries as well anyway

What are you trying to achieve?

Provide us some sample data, and the expected result.
You might find you don't need the function at all.
0
 

Author Comment

by:brucegust
ID: 41731231
Paul, I'm a contract developer that's been hired to plug the holes and fix some glitches in a program that's being retired before the end of the year. The code is several years worth various programmers coming in and adding yet another layer of syntax, the result being a convoluted mess -or at least a body of code that can be very difficult to uncoil sometimes.

This is a function that's part of a SELECT statement. The result of this particular "piece" dictates a fork in the road later on. However ironic as it may sound, I'm actually encouraged by your impression because it's the biggest suspect right now, as far as why something is not working the way that it should.

As far as "sample data," not sure if you're looking for some schemas and tables - I hope not. All I want is just some direction as how I could assign some values to @ParmClientID BIGINT,
      @ParmItem VARCHAR(200)  and then fire it in MSSQL Studio.

What do you think?
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41732075
just us a select query

            select dbo.ufn_ClientPreference( 123456  , 'StatementRollUp' )

where 123456 is some integer you nominate
0
 

Author Closing Comment

by:brucegust
ID: 41735818
Perfect!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question