Avatar of bfuchs
bfuchs
Flag for United States of America asked on

Need help in debugging a UDF results

Hi Experts,

I have a function that accepts some parameters and returns a results string.
See attached.

Now I need some help figuring out why the section below only works when the results (@MissingDocs)  includes "Missing: License" as part of the wording, otherwise I dont see it working..

if @BclsExpires between getdate() and getdate()+ 60
	begin
		select @WillExpire = @WillExpire + ', Bcls'
		select @IsBclsDate = 1
	end
if @AclsExpires between getdate() and getdate()+ 60
	begin
		select @WillExpire = @WillExpire + ', Acls'
		select @IsAclsDate = 1
	end
if @NalsExpires between getdate() and getdate()+ 60
	begin
		select @WillExpire = @WillExpire + ', Nals'
		select @IsNalsDate = 1
	end
if @PalsExpires between getdate() and getdate()+ 60
	begin
		select @WillExpire = @WillExpire + ', Pals'
		select @IsPalsDate = 1
	end	

Open in new window

Basically I'm looking for some hints on how to debug this, as I'm using SQL 2005 where I cannot use debuggers tools..
MyFunc.txt
Microsoft SQL Server* T-SQLDatabasesMicrosoft SQL Server 2005MongoDB

Avatar of undefined
Last Comment
bfuchs

8/22/2022 - Mon
Pavel Celba

To tell why it does not work for you we would need parameter values used for the function call.

You may debug this function simply in SSMS:
Place the whole function to the New query window, remove lines which define the function:
ALTER FUNCTION [dbo].[EmployeesMissingDocs] (
) RETURNS VARCHAR(2000)
AS

and add DECLARE keyword on top of the function parameters. Then simply add parameter values and you may start debugging.

The whole function is a candidate to rewrite. You shouldn't use special parameters for each piece of document/measurement/value but rather process the table where all these values are stored in (hopefully) better structure. To add some new requirement to this function is close to the nightmare...
bfuchs

ASKER
@pcelba,
and add DECLARE keyword on top of the function parameters. Then simply add parameter values
Can you give me example of how it looks like?
and you may start debugging.
How? I would love to have the ability to step into code line by line (like I have in VBA) however as mentioned my SQL version doesn't support it..
To tell why it does not work for you we would need parameter values used for the function call.
I may get that for you shortly

Thanks,
Ben
bfuchs

ASKER
Oh I thought would get that from profiler, however I see profiler doesn't provide all details just displays the function that is calling the function is question
see below what I got from there
 SELECT * FROM fn_frmJcahoReportsFrm(562,571) WHERE  Facility1_LastDay >= '1/20/2017' AND ID = 148223 ORDER BY JcahoDueDate 

Open in new window

Any idea how to get all params being sent from my app?

Thanks,
Ben
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
bfuchs

ASKER
Yes, that was indeed the culprit.
Thank you my experts!