Quick Tips For Debugging Large Stored Procedures

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.

IF @MySQLInjectionGift IS NULL BEGIN YELL ABOUT IT BEING NULL END;

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.

Wishlist

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责编内容来自:Brent Ozar PLF (源链) | 更多关于

阅读提示:酷辣虫无法对本内容的真实性提供任何保证,请自行验证并承担相关的风险与后果!
本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 综合技术 » Quick Tips For Debugging Large Stored Procedures

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录