Use The Easy Debugging Feature with HerbSQL

A code generator may evolve to the point where there are hundreds of parsing elements which can make it difficult to identify the source of a problem or an area where you would like to introduce a change. The idea that I have introduced in my approach is to simply flip a switch whch will then provide you an output that makes it easy to find the source of the problem.

In my previous post, Easy way to generate statements and debug them. "Herb SQL" I provided an example of a procedure to generate a SQL Stored procedure which will update table columns using the Primary Key columns as predicates.

I provided an example of how to run the stored procedure and obtain the genated code.

Note that the @debug value supplied was 0 indicating that the debug feature is not to be invoked or False. Now I would like to have the procedure executed with the debug feature invoked.

Now run the procedure with the @debug value set to 1 or True in order to invoke the debug functionality.

This time the output supplied has a reference to the place in the code generator which generated the output line. The generated code output "CREATE PROCEDURE [HumanResources].[usp_Upd_Department]" was generated based on the location in the generator where the indicator ':0010:' is found. Now you can search the generator code to determine exactly where that line is formatted making it very easy to identify the area to correct or change. It is that simple.

Department:0000:0000| IF OBJECT_ID('[HumanResources].[usp_Upd_Department]','P') IS NOT NULL

Department:0000:0001| DROP PROCEDURE [HumanResources].[usp_Upd_Department]

Department:0000:0002| GO

Department:0010:0000| CREATE PROCEDURE [HumanResources].[usp_Upd_Department]

Department:0030:0001| @DepartmentID smallint

Department:0030:0002| ,@Name nvarchar(50)

Department:0030:0003| ,@GroupName nvarchar(50)

Department:0030:0004| ,@ModifiedDate datetime

Department:0100:0000| AS

Department:0300:0000| UPDATE [HumanResources].[Department]

Department:0500:0002| SET Name = @Name

Department:0500:0003| , GroupName = @GroupName

Department:0500:0004| , ModifiedDate = @ModifiedDate

Department:0600:0001| Where DepartmentID = @DepartmentID

Department:9000:0000| GO

Once you find exactly where the code was generated it is a small matter to make an update and test it.

This feature allows you to quickly find the source of a problem or quickly repurpose a generator that already exists to accomplish another purpose. You don't have to sift through all of the code, you can identify the area that you want to change and go right to that spot.

This technique has allowed me to generate and apapt simular code to evolving needs very quickly and same countless hours of tedious work.

I hope that you will try this approach and experience simular results.