Quick Tips For Debugging Large Stored Procedures

综合技术 2018-05-16 阅读原文

Hell Here! Get Your Fresh Hell, Here!

Let’s face it — as far as developer tools go, SSMS is pretty bad.

Intellisense? The jokes write themselves.

Don’t get me wrong, SSMS is a good management tool , but it’s not a good development tool (and what kind of maniac sticks debug — F6 — next to execute — F5 — ?).

When you’re writing some simple queries, it’s fine.

When you’re writing code with lots of branching, dynamic SQL, temp tables, error handling, and so on… Notsomuch.

Here are some tips I’ve put together after a couple years of wrangling the Blitz scripts in the First Responder Kit .

Tip #1: Format Your Code

There’s no shortage of free and paid tools out there. This list from the Recently Legendary Aaron Bertrand on Stack Exchange has both.

  • http://www.red-gate.com/products/sql-development/sql-prompt/
  • http://www.apexsql.com/sql_tools_refactor.aspx
  • http://www.dpriver.com/pp/sqlformat.htm
  • http://www.cleancss.com/sql-beautify/
  • http://www.sql-format.com/
  • http://poorsql.com/
  • http://www.freeformatter.com/sql-formatter.html

Normalization isn’t just for data. Normalized coding patterns help you keep things organized.

This really helps in those sticky situations where you have a BEGIN without an END, a missing or misplaced parenthesis, or some single-tick nightmare of nested, nested, nested dynamic SQL.

Tip #2: Always Be Debugging

If you’re writing dynamic SQL, always add a Debug mode. It doesn’t have to be anything fancy at first, just something like:

IF @Debug = 1 BEGIN PRINT @MySQLInjectionGift END;

If you find yourself writing blocks of dynamic SQL that don’t fit in a single PRINT output, Helper_Longprint by Yildirim Kocdag is awesome.

When you get further into things, you may also want to build in a no-execute mode so you’re not off running bad dynamic SQL blocks.

Tip #3: Keep Track Of Temp Tables

… And I guess table variables, too.

In a lot of the Blitz procs where we use temporary objects, I’ll set up a Debug block that will select data out of all the temp tables we’ve put data into.

Of course, if you have a lot of temp tables, it’s easy to get lost. When I set up debugging for those objects, I like to include the table name as a column, too.

This makes sense.

Now you can see what did (or didn’t) end up in your temp tables.

Tip #4: Know Your Place

Don’t be afraid to use RAISERROR to mark off sections of your code. This also belongs in a Debug mode so you’re not needlessly returning noise, but can be super helpful for you to figure out things like which code path was hit and which section throws an error executing common code.

Why RAISERROR over PRINT? Nice message formatting, and you can use WITH NOWAIT to print messages immediately.

With PRINT, you may be stuck waiting for enough buffers to fill up to display a message.

Tip #5: Know Your NULLs

This is another one for dynamic SQL peoples. It’s so obvious, you’ll kick yourself if you haven’t already thought of it.


When I first started writing dynamic SQL, I can’t tell you how many times I got eaten alive by a NULL variable or value making my whole string NULL.

Which brings us to our last tip!

Tip #6: Know Your Variables

There are two ways you can do this. Either create a variable logging temp table and insert the variable name and value to it, or just use RAISERROR to print the contents to the console. In either case, this is a life saver for figuring out what values got assigned things and when.

If variables change assignment frequently, RAISERROR is a better option. If they get assigned once, the logging table is just fine.


While I wish SSMS, and SQL Server, had better ways of handling dynamic SQL, color coding parenthesis pairs, and matching BEGIN/END blocks better, those just aren’t built-in tools or features currently.

Many tools that are better for development aren’t necessarily better (or even good) for management. It’s a pain to switch back and forth if you need to do both regularly. Third party tools have filled many gaps, but I reckon there’s just some behavior that can’t be altered unless you get into the actual bits behind SSMS.

Maybe someday.

Thanks for reading!

Brent Ozar PLF

责编内容by:Brent Ozar PLF阅读原文】。感谢您的支持!