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 NULL
DROP PROC dbo.ChildSproc
CREATE PROC dbo.ChildSproc
SET NOCOUNT ON;
SELECT @input [output];
IF OBJECT_ID('dbo.ParentSproc','P') IS NOT NULL
DROP PROC dbo.ParentSproc;
CREATE PROC dbo.ParentSproc
SET NOCOUNT ON;
IF LEN(@input) < 10
EXEC ChildSproc 'First Option Executed';
EXEC ChildSproc 'Second Option Executed';
DECLARE @someParam nvarchar(40);
SET @someParam = @input + ' (more text here)';
EXEC dbo.ChildSproc @someParam;
If I execute the following:
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:
- To Not To. Again, debugging should be done on a dev/staging/test server whenever possible. Make sure you have no other options before turning on an aggressive Profiler trace out in production. (And another, less expensive, option would be Extended Events.) And, while it's POSSIBLE to do this with a decent set of filters, it's a bad idea to get 'comfortable' debugging in prod in many cases because while operation might not cause problems, there's no telling what the 2nd operation or 20th might do if you're not being cautious.
- Use Suitable Filters. Causing the server to spit out details about EVERY statement being executed in EVERY sproc can quickly generate an absurd™ amount of data. So, if you're targeting just a problematic sproc or two, then drop their OBJECT_IDs into the Column Filters for profiler. So, for example, by querying SELECT OBJECT_ID('ParentSproc') and then doing the same for ChildSproc, I was able to obtain the Object_ids for both of these sprocs, and then dropped them into the appropriate Column Filter as shown below:
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.