Easy Trick to Debug Long TSQL Codes

In my previous organization, working as Senior SQL Developer, I was often asked for help by fellow colleagues for identifying issues inside long and complex TSQL codes. Finding issues becomes much trickier if the issue is Runtime error.

So, as a solution for finding out issues in TSQL code, we have 2 methods:

  1. Using the Transact-SQL Debugger
    The Transact-SQL debugger in SQL Server Management Studio enables you to find errors in Transact-SQL scripts, stored procedures, triggers, and functions by observing their run-time behavior. You can start the debugger when you are using the Database Engine Query Editor window. By using the Transact-SQL debugger, you can do the following:

    • Step through the Transact-SQL statements in the editor line by line, or set breakpoints to stop at specific lines.
    • Step into or over Transact-SQL stored procedures, functions, or triggers that are run by the code in the editor window.
    • Watch the values that are assigned to variables, and observe system objects such as the call stack and threads.

    Below video by kudvenkat explains T-SQL Debugger in easy way.

    The only problem with this approach is slowness when we are debugging code from SSMS away from Server machine.

  2. Using intermittent message with tsql Print statement
    Let’s understand this by example. Recently I published a blog post with title Create/Restrict Data Files – Automation. I was using the same script on one of our server when I received below runtime error message:-

    In order resolve this, I redesigned the code to print intermittent messages inside TSQL code. So, below is the final query.

    In above code, I have declared a variable @_verbose at line 52. After that I am printing message for debugging purpose if value for @_verbos is equal to 1. This way I get to know which portion of my code is not working or throwing error.

    Now, below is the output from above modified query:-
    Finally, once our TSQL code debugging is complete, we can set off all the Print Messages just by setting the value of @_verbos variable to 0 at line 52. So, this way, we can debug our complex and long TSQL code just by changing the value of @_verbos variable from 0 to 1, and 1 to 0.

Friends, if you like the article, do Like & Share. Happy Coding 🙂

Leave a Reply