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.

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

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

您可能感兴趣的

Unable to launch the object of type ‘System&... I have to get count of rows based null value on one specific column. I have u...
Restore an Azure SQL Database from one server to a... By: Mohammed Moinudheen | | Related Tips:More >Azure Free Webca...
Mysql之你不得不知道的–事务transaction,数据库编程procedure ,... 事务 事务其实就是针对一组Sql语句,要么全部成功,要么全都失败,就比如银行转账,一个人转钱,一个人收钱,要么就是转钱成功,收钱也成功,要么就是转钱失...
Complex SQL query – nested query Here is the job_form table... job_num | name | address | ---------+------+---...
Ali-HBase的SQL实践与改进 2017云栖大会Hbase专场,阿里巴巴技术专家天穆带来Ali-HBase的SQL实践与改进的演讲。本文主要从为什么需要SQL开始谈起,进而讲解了SQL on ...