How to trace T-SQL function calls

tsql debugging trace user-defined-functions
Daren Thomas picture Daren Thomas · Dec 9, 2008 · Viewed 28.6k times · Source

I'm trying to debug a rather complicated formula evaluator written in T-SQL UDFs (don't ask) that recursively (but indirectly through an intermediate function) calls itself, blah, blah.

And, of course, we have a bug.

Now, using PRINT statements (that can then be read from ADO.NET by implementing a handler for the InfoMessage event), I can simulate a trace for stored procedures.

Doing the same for UDF results in a compile time message:

Invalid use of side-effecting or time-dependent operator in 'PRINT' within a function.

I get the message (PRINT does some stuff like resetting @@ROWCOUNT which definitly is a no-no in UDFs, but how can I trace through the calls? I want to have this trace printed out, so I can study it without getting distracted by stepping through the calls in the debugger...

EDIT: I have tried to use the SQL Profiler (this was a first time one for me), but I can't figure out what to trace for: Although I can get the trace to output the queries sent to the database, they are opaque in the sense that I can't drill down to the Expression-UDFs called: I can trace the actual Stored Procedure invoked, but the UDFs called by this procedure are not listed. Am I missing something? I guess not...

EDIT #2: Allthough the (auto-)accepted answer does trace the function calls - very helpful, thanks - it does not help in finding out what parameters were passed to the function. This, of course, is essential in debugging recursive functions. I will post if I find any sollution at all...

Answer

SqlACID picture SqlACID · Dec 9, 2008

Why not use SQL Profiler with statement level events added?

Edit: Add events for Stored Procedures : SP:Stmt Starting or SP:Stmt Completed Use variables to debug if needed, i.e. set @debug='i am here'; UDF's, while not technically stored procedures, will get traced with the statement level events.

Related questions

How to debug SQL Server T-SQL in Visual Studio 2012

How does one debug a T-SQL stored procedure in a multi-tier application in Visual Studio 2012? To be clear, I want to set a breakpoint in a sproc in VS 2012, and hit it when the sproc is called from an ASP.…

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More