Debugging (Nested) Stored Procedures with SQL Server Profiler

Nested stored procedures (or stored procedures that call other stored procedures) are a mixed blessing and a curse. They also serve as powerful fodder for starting holy wars. On the one hand, they serve as a great means for allowing developers to re-use code and thereby avoid DRY. On the other hand, they can also cause performance problems given that the very ability to re-use code implies that code can (and will—eventually) be used in ways that the original designers might not ever have anticipated. That, and they can be tricky to debug—because if a 'parent' stored procedure might potentially make a call to one or more ‘child’ (or nested) stored procedures, then it can be hard to get a sense for what’s going on 'internally'—or within that child procedure.

Related: SQL Server's Trace and Replay Tool

Debugging Options Other than SQL Server Profiler

One obvious option that should come to mind for debugging complex sprocs would be built-in debugging functionality. I personally haven't used that capability on or with or against any SQL Server in over a decade. I've just had too many bad experiences jumping through hoop after hoop trying to get that set up in a distributed environment in the past – to the point where I honestly won't even bother trying anymore.

That, and debugging just isn't EVER an option in a production setting. Ever.

Extended Events are also an option for trying to do more or less what I'm outlining here—but that's another blog post. As such, this post deals with some tricks you can use with SQL Server Profiler.

Using SQL Server Profiler’s Ability to Peek into Sproc Execution Details

Out of the box, a default SQL Server Profiler trace typically just looks at batch and RPC level events. But there's a whole category of SP (Stored Procedure) events that can be used to peak in on the actual 'goings on' inside of a sproc when it is run – including when it makes calls to other sprocs (or, more specifically: there's enough detail provided that you can SEE when 'child' sprocs are called).

So, with the following Events specified:

And with the following code:

IF OBJECT_ID('dbo.ChildSproc','P') IS NOT NULLDROP PROC dbo.ChildSprocGOCREATE PROC dbo.ChildSproc@input nvarchar(40)ASSET NOCOUNT ON;SELECT @input [output];RETURN 0;GOIF OBJECT_ID('dbo.ParentSproc','P') IS NOT NULLDROP PROC dbo.ParentSproc;GOCREATE PROC dbo.ParentSproc@input nvarchar(20)ASSET NOCOUNT ON;IF LEN(@input) < 10EXEC ChildSproc 'First Option Executed';ELSE EXEC ChildSproc 'Second Option Executed';  DECLARE @someParam nvarchar(40);SET @someParam = @input + ' (more text here)';EXEC dbo.ChildSproc @someParam;RETURN 0;GO

If I execute the following:

EXEC Parent'Text';

Then I’ll end up with the following:

And note that I’ve highlighted how you won’t actually get to see what the value of @someParam is—because ChildSproc is, in this case, being called with a parameter as input. Note, in contrast, however, that about 4-5 lines up, you DO see exactly what's being passed into ChildSproc—because it’s being called with data instead of data 'in-lined' into parameters.

Some Words of Caution

Of course, just as you wouldn’t want to EVER debug code out in production (as doing so can/will serialize all execution through your paused debugging thread), there are some inherent risks with turning on SP:StmtStarted/Completed as well—in that these events provide a statement-by-statement view into everything that's executing within a sproc on your server. As such, IF you NEED to 'debug' out in production by using Profiler in the fashion outlined above, I’d recommend the following:

Which, in turn, constrains statement output and details to JUST statement-level details coming out of either of these sprocs.

Otherwise, the ability to 'peer' into the actual 'goings on' within an executing stored procedure via SQL Server Profiler is a decent 'trick' that you can use in SOME cases to help debug complex dependency chains and calls that might show up in some of your sprocs.