How to debug User Defined Function ?

Question

How to debug User Defined Function ?

Dimitri

SSC Eights!

Points: 808
More actions
December 5, 2006 at 7:25 am

#115383

Hi all,
How are you debugging user-defined function ?
Is it possible to add "trace" informations ?
Is it possible to store debug informations in a table ?
Any info would be appreciated.
Thanks

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Is there a specific problem that you are having? If so, it would be helpfull if you would post your UDF and provide a description of your problem and include an example of what you expect your UDF to produce.

I do not understand what you are asking for when you ask about 'tracing' UDF's. Are you asking about using Profiler to trace UDF execution?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Sergiy SSC Guru Points: 110209 More actions · Answer 2

What's stopping you from debugging the code without encapsulating it into UDF?

Do it in QA first, test performance, check execution plan, and when you are absolutely happy with it add "CREATE FUNCTION" statement on top.

_____________
Code for TallyGenerator

Dimitri SSC Eights! Points: 808 More actions · Answer 3

When I talked about tracing, I would like to show intermediate values in calculations.

I don't know if it's possible using Profiler.

Sergiy SSC Guru Points: 110209 More actions · Answer 4

Show where?

Function may have only one outcome - its result.

I tell you again - debug not function, debug the code in function.

_____________
Code for TallyGenerator

Dimitri SSC Eights! Points: 808 More actions · Answer 5

I've found a way to get intermediate values back from my function.

Here is my example :

create function dbo.MyFunction(@Arg1 int, @Arg2 int)

returns @DebugTable TABLE(Col1 varchar(255) null)

as

begin

declare @MyVariable int

--

-- some calculations to initialise @MyVariable

--

select @MyVariable = @Arg1 * @Arg2

--

-- store intermediate value

--

insert @DebugTable values('Intermediate value of @MyVariable='+convert(varchar,@MyVariable))

--

-- some other calculations with @MyVariable

--

-- .......

select @MyVariable = @MyVariable + 1

--

-- store another intermediate value

--

insert @DebugTable values('Intermediate value of @MyVariable='+convert(varchar,@MyVariable))

return

end

go

select * from dbo.MyFunction(5,5)

go

drop function dbo.MyFunction

go