Debugging SQL Objects

If you are using Visual Studio and open up a SQL object (proc, function, etc) you have the option to right click and set a breakpoint much like you can with C# or VB.NET, etc.

If you are a purist (I guess I would call myself one) then you want to do all your SQL stuff in SQL.

A great way to debug objects in the database is to use a statement to output the various aspects of the execution of your objects to the output window in your database tool.

In MS SQL, the command you would want to use is PRINT.  In the example below, I have a procedure that validates some number.  And my business requirement is that this number cannot be less than 0.



As you can see here, its very simple to put these print statements in code.  This is a very simple example but you can imagine having a complicated set of code where you needed to be able to run it and get meaninful information regarding the execution, this would be a great way to do that.  You could throw exceptions as well but that's another topic for another day.

This last screenshot is what the output looks like when I tested my object.

You can do the same thing in Oracle, but the command you would want to use is DBMS_OUTPUT.PUT_LINE.