Poor Man s Debugger


Poor Man's Debugger

You can debug your stored procedures even if you do not have Visual Studio. Before debuggers became part of the programming environment, developers used simple techniques to print the contents of variables and follow the execution of code. Some programming languages include commands (for instance, Assert in Visual Basic 6.0) that are active only during debugging. In others, you simply add print commands during the development stage and comment them out before releasing the code into production.

In Transact-SQL, I use a very simple technique that allows me to view the contents of the variables and recordsets when I am testing a stored procedure from the Query window. I add one additional parameter with the default set to 0 to the stored procedure:

      @debug int = 0 

At all important points in the stored procedure, I add code that tests the value of the ©debug variable and displays the values of selected variables or result sets:

      if @debug <> 0      select @chvProperty Property,             @chvValue [Value],             @chvUnit [Unit]      . . .      if @debug <> 0         select * from #Properties 

I do not use the Print statement for this purpose because

  • It does not support the display of result sets.

  • In older versions, it was impossible to concatenate a string inside a Print statement.

  • Some client utilities and applications handle messages from the Print statement differently than they do the result set from the Select statement.

  • If the procedure was moved into production without removing the debug code, the debug code would not be executed due to the value of the variable. If Print statements were inadvertently left in procedures when they were moved into production, this would present a problem for the application.

In the following example, you can see a stored procedure that is designed to support this kind of testing:

      ALTER Procedure [dbo].[ap_InventoryProperties_Get]      /************************************************************      Return comma-delimited list of properties that are describing asset.      i.e.: Property = Value Unit;Property = Value Unit;Property = Value       Unit;Property = Value Unit;Property = Value Unit;...      test:      declare @p varchar(max)      exec ap_InventoryProperties_Get 5, @p OUTPUT, 1      select @p      **************************************************************/           (                @intInventoryId int,                @chvProperties varchar(max) OUTPUT,                @debug int = 0      )     As      declare @intCountProperties int,              @intCounter int,              @chvProperty varchar(50),              @chvValue varchar(50),              @chvUnit varchar(50) ,           @chvProcedure sysname      set @chvProcedure = 'ap_InventoryProperties_Get'      if @debug <> 0            select '**** '+ @chvProcedure + 'START ****'      Create table #Properties(                Id int identity(1,1),                Property varchar(50),                Value varchar(50),                Unit varchar(50))      -- identify Properties associated with asset      insert into #Properties (Property, Value, Unit)           select Property, Value, Unit           from dbo.InventoryProperty InventoryProperty              inner join dbo.Property Property          on InventoryProperty.PropertyId = Property.PropertyId      where InventoryProperty. InventoryId = (SintInventoryId      if @debug = 1        select * from #Properties      -- set loop      select @intCountProperties = Count (*),          @intCounter = 1,          @chvProperties = ' '      from #Properties      -- loop through list of properties      while @intCounter >= @intCountProperties      begin          -- get one property          select @chvProperty = Property,               @chvValue = Value,               @chvUnit = Unit      from #Properties      where Id = @intCounter      if @debug <> 0          select     @chvProperty Property,                     @chvValue [Value],                     @chvUnit [Unit]      -- assemble list      set @chvProperties = @chvProperties + ' ; '                         + @chvProperty + ' = '                         + @chvValue + ' ' + ISNULL (@chvUnit, '')      if @debug = 1         select @chvProperties [@chvProperties], @intCounter [@intCounter]          -- let's go another round and get another property          set @intCounter = @intCounter + 1      end      if Substring (@chvProperties, 0, 2) = '; '         set @chvProperties = Right (@chvProperties, Len (@chvProperties) - 2)      drop table #Properties      if @debug <> 0          select '**** '+ OchvProcedure + 'END ****'      return 0 

To debug or test a stored procedure, I execute the stored procedure from the Query window with the ©debug parameter set to 1:

      declare @chvResult varchar(max)      exec dbo. ap_InventoryProperties_Get'           @intInventoryId = 5,           @chvProperties = @chvResult OUTPUT,           @debug = 1      select @chvResult Result 

Remember that you can pass parameters either by name or by position. The result of the execution will be an elaborate printout like the one shown in Figure 16-4.

image from book
Figure 16-4: Result of execution of a stored procedure in "poor man's debugger mode"

Execution in the Production Environment

In production, the stored procedure is called without a reference to the ©debug parameter. Here, SQL Server assigns a default value to the parameter (0), and the stored procedure is executed without debug statements:

      exec dbo. ap_InventoryProperties_Get'                @intInventoryId = 5,                @chvProperties = @chvResult OUTPUT 

Nested Stored Procedures

Two tricks can help you debug a set of nested stored procedures (that is, when a stored procedure calls another stored procedure). It is a useful practice to display the name of the stored procedure at the beginning and end of the stored procedure:

      declare @chvProcedure sysname      set @chvProcedure = 'ap_InventoryProperties_Get''      if @debug <> 0            select '**** '+ OchvProcedure + 'START ****'      . . .      if @debug <> 0            select '**** '+ s@chvProcedure + 'END ****'      return 0 

When you call a nested stored procedure, you need to pass the value of the ©debug parameter to it as well. In this way, you will be able to see its debugging information.

      exec dbo.ap_InventoryProperties_Get' @intInventoryId,      @chvProperties OUTPUT,      @debug 

Output Clause

A new feature of SQL Server 2005 is the Output clause of DML statements. It gives access to the set of affected records. Similar to triggers, it is based on the usage of Inserted and Deleted tables. In its simplest form, it is used to return records that were altered to the caller. If you execute the following statement, you will return records that were modified (in their original state) as a result:

      Update dbo.EquipmentBC      Set EqBC = '111'         OUTPUT DELETED.*      Where EqID = 1 

Naturally, you could reference the Inserted table to return records in a transformed state. The following example demonstrates an interesting effect. The Output clause can access fields that are being generated (such as an identify field):

      INSERT dbo.Equipment2(EqTypelD, Make, Model)          Output Inserted.EqId, Inserted.EqTypeID,                 Inserted.Make + ' - ' + Inserted.Model      VALUES (22, N'Alfa', '2000X'}; 

It is possible to do this because the Output clause works like the After trigger at the end of the statement's execution. You can also show the result of computed columns or include expressions based on columns in the Output clause.

Note 

The Output clause cannot return the result to the console on tables that have enabled triggers. I guess that the problem has something to do with the fact that both the Output clause and trigger use Inserted and Deleted virtual tables. Theoretically, you can set the trigger to return something to the console, but that is not recommended practice. It is better to use table variables (described in the following text).

From time to time it is useful to return original (deleted) or transformed (inserted) records along with some other data. You cannot use the Join clause within the Output clause, but there is a workaround. The Output clause can send the result to the table variable, temporary table, or static table, instead of to the console:

      begin tran      declare @tbl table (EqId int,                          EqTypeID int,                          Eq varchar(100));       delete dbo.Equipment2          OUTPUT Deleted.EqId, Deleted.EqTypeID,                 Deleted.Make + ' - ' + Deleted.Model          into @tbl      where Make like 'C%';      select t.*, EqType.EqType      from @tbl t inner join EqType      on t.EqTypeID = EqType.EqTypeID 

The biggest disadvantage of this method is that you have to know the schema of the table variable in advance.




Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL &  .NET
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
ISBN: 0072262281
EAN: 2147483647
Year: 2006
Pages: 165

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net