asked on 3/21/2017

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-SQL Databases Microsoft SQL Server 2005 MongoDB

Last Comment

bfuchs

3/21/2017

Pavel Celba

3/21/2017

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

3/21/2017

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

3/21/2017

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

ASKER CERTIFIED SOLUTION

Scott Pletcher

3/21/2017

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

SOLUTION

Pavel Celba

3/21/2017

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

SOLUTION

Scott Pletcher

3/21/2017

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

bfuchs

3/21/2017

ASKER

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