Solved

How can I test this function?

Posted on 2016-07-27
4
60 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL STANDARD CORE 8 38
Stored Proc - Rewrite 42 59
SQL create line numbers for data sampling 11 28
AD and SQL Server 2016 2 29
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

829 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