How I Set Up Debugging In SQL Server Stored Procedures
HTML-код
- Опубликовано: 6 сен 2024
- 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...
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.
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 😉
Great as always. Much thanks Erik!
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?
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.
@@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…”
@@SQLDevDBA hahaha, yeah, I know how that goes. If it's that important, it wouldn't take anyone three days to ask.
@@ErikDarlingData so true!
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
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.
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. :)
I do, sort of. You can see it here: github.com/erikdarlingdata/DarlingData/blob/main/SQL%20Debug%20Example/DebugExample.sql