How I Set Up Debugging In SQL Server Stored Procedures

Become a member! / @erikdarlingdata Code: github.com/eri...
If you like what you see here, you'll love my advanced performance tuning training:
training.erikd... Click here for 50% off a health check: training.erikd...

[@gurumalcolm]

Yep - possibly the best video - this week anyway :-), A twist on the theme I commonly use is a debug level rather than a simple on/off. Thanks for this.

[@ErikDarlingData]

Well, yeah, but debug levels often end up like those toilets with two flush buttons on them: everyone always goes for the one with the highest number 😉

[@esatsaglam2800]

Great as always. Much thanks Erik!

[@SQLDevDBA]

Thanks Erik this is awesome. What’s your opinion on deploying a customized but semi-universal Exception Log table and logging there? Like RaisError but to a table?

[@ErikDarlingData]

It depends a bit on who needs to see it. I think application logging would be the better choice here in most cases. A table like that may end up like one of those huge audit tables that no one ever looks at.

[@SQLDevDBA]

@@ErikDarlingData great point! I did use one in the past and it indeed became one of those huge tables. The DAY after I purged old records was usually the day I got a “hey what was that error that happened 3 years ago in the procedure we never use? It’s really important…”

[@ErikDarlingData]

@@SQLDevDBA hahaha, yeah, I know how that goes. If it's that important, it wouldn't take anyone three days to ask.

[@SQLDevDBA]

@@ErikDarlingData so true!

[@eliassal1]

Thanks Erik, good ideas and it seems you have put a lot of efforts to achieve this. But, if I understand well, correct me if I am mistaken, this is an example to include "Debug Activity" inside a SP, so this means that all SPs need to be written and in this case code will be repeated. I thought in the begining that it was a SP that helps debugging a SP by running something like DebugFunctionProc @name = MySP or pointing to a sql file where we can extract the T-SQL, what do you think? So now if I need to use your example, I need to repalce the @s value in your debugExample SP with mine, is this understanding correct? Thanks again for making this available for the communiy

[@ErikDarlingData]

You can totally set up a global debug procedure to do things like this and more (like log things to tables). I don't do that for my procedures (the ones in my GitHub repo) because I don't want to introduce an additional dependency for anyone to have to download and install. In my code, there's lots of repetition to do this stuff because of that.

[@PeterSchott]

Do you have a template you use for all of this debugging logic or do you piecemeal it in as needed? Really appreciating this as it's the sort of thing I always wish I'd done when I run into trouble with a stored proc. :)

[@ErikDarlingData]

I do, sort of. You can see it here: github.com/erikdarlingdata/DarlingData/blob/main/SQL%20Debug%20Example/DebugExample.sql