Debugging T-SQL stored procedures without debugger


Link to this posting

Postby Ursego » 21 Aug 2018, 19:09

The syntax is for the Sybase ASE dialect of T-SQL (Transact-SQL) - NOT tested on MS SQL Server.

Sometimes debugging of stored procedures is not a very trivial task. For example, if the proc has a lot of input parameters, which are normally passed from the client application (like 70-80 values, sent to be saved from a data entry form), you are required to supply them (or part of them) prior to running the debugger. In that situation, it's much easier to insert the tested information (variables values, or an evidence that a code fragment is reached) into a table, created specially for debug purpose. So, first of all, please run this statement:

Code: Select all
CREATE TABLE debug_msg
(
    counter_for_sort int NOT NULL,
    proc_name varchar(30) NOT NULL,
    msg varchar(1000) NOT NULL,
    added_dt datetime NOT NULL,
    added_by varchar(15) NOT NULL
)

Since it's not exceptionally convenient to write an INSERT statement each time you want to log something, you are provided with a stored proc which does the "black work". Its header comment contains examples of use - ready fragments, which only require to add your dame and DB username:

Code: Select all
CREATE PROCEDURE i_debug_msg
    @user_name   varchar(15)   -- always hardcode your user name ('eXXXXX'); don't use suser_name()!!!
   ,@proc_id     int           -- pass local var @proc_id which has been populated from @@procid; don't pass @@procid directly!!!
   ,@dbg_err_msg varchar(500)
   ,@dbg_err_no  int = 0       -- pass local var @dbg_err_no which has been populated from @@error; don't pass @@error directly!!!
   ,@del_old     char(1) = 'Y' -- pass 'N' if you want to keep records, inserted previously (if the algorithm being debugged runs longer than 15 seconds)
AS
/******************************************************************************************************************************************
INSERTs a debug messgage INTO debug_msg table.
*******************************************************************************************************************************************
Examples of use:

####### STEP 1: Add in the proc's top:

-- <Your Name> debug code - BEGIN
DECLARE @dbg_err_msg varchar(500), @dbg_err_no int, @dbg_rowcount int, @proc_id int
SET @proc_id = @@procid
EXEC i_debug_msg @user_name = 'eXXXXX', @proc_id = @proc_id, @dbg_err_msg = 'START'
-- <Your Name> debug code - END

####### STEP 2: Call i_debug_msg in the location(s) you need:

@@@ IF the debug message is hardcoded (only to check if the code fragment is reached):

EXEC i_debug_msg @user_name = 'eXXXXX', @proc_id = @proc_id, @dbg_err_msg = '<<1>>' -- <Your Name> debug code

@@@ IF the debug message is built dynamically (for example, to check variables' values):

-- <Your Name> debug code - BEGIN
SET @dbg_err_msg = 'BEFORE CALLING i_sa_XXX'
               + ', @pol_no='          + CONVERT(VARCHAR, @pol_no)
               + ', @pol_ver_dt='      + CONVERT(VARCHAR, @pol_ver_dt)
               + ', @pol_item_ins_no=' + CONVERT(VARCHAR, @pol_item_ins_no)
EXEC i_debug_msg @user_name = 'eXXXXX', @proc_id = @proc_id, @dbg_err_msg = @dbg_err_msg
-- <Your Name> debug code - END

@@@ To check the results of an SQL statement (@@rowcount & @@error):

-- <Your Name> debug code - BEGIN
SET @dbg_err_no = @@error, @dbg_rowcount = @@rowcount
SET @dbg_err_msg = 'AFTER SELECT ... FROM ...'
               + ', @@rowcount='       + CONVERT(VARCHAR, @dbg_rowcount)
               + ', @pol_no='          + CONVERT(VARCHAR, @pol_no)
               + ', @pol_ver_dt='      + CONVERT(VARCHAR, @pol_ver_dt)
               + ', @pol_item_ins_no=' + CONVERT(VARCHAR, @pol_item_ins_no)
EXEC i_debug_msg @user_name = 'eXXXXX', @proc_id = @proc_id, @dbg_err_msg = @dbg_err_msg, @dbg_err_no = @dbg_err_no
-- <Your Name> debug code - END

####### To see the debug results, run this SQL:

SELECT counter_for_sort AS id, proc_name, msg, added_dt FROM debug_msg WHERE added_by = suser_name() ORDER BY counter_for_sort
*******************************************************************************************************************************************
Michael Zuskin 06-Jun-2018 Created
******************************************************************************************************************************************/
DECLARE
    @err_desc         varchar(500)
   ,@proc_name        varchar(50)
   ,@now_dt           datetime
   ,@counter_for_sort int

-- This condition allows you to call i_debug_msg NOT ornamented with "IF suser_name() = 'eXXXXX'":
IF @user_name <> suser_name() RETURN

SELECT @now_dt = GetDate()

IF @dbg_err_no <> 0 BEGIN
   SELECT @err_desc = description FROM master..sysmessages WHERE error = @dbg_err_no
   SET @dbg_err_msg = @dbg_err_msg + ' DB Error: ' + @err_desc
END

-- Delete old records (i.e. records not belonging to this run) so that you will always see only the results of the last run.
-- Records are considered old if they are older than 15 seconds - assuming that the algorithm being debugged runs 15 seconds or less.
-- If the algorithm runs longer than 15 seconds, then the first debug messages will be lost. To prevent that, pass arg @del_old = 'N':
IF @del_old = 'Y'
   DELETE FROM debug_msg WHERE added_by = @user_name AND DateDiff(second, added_dt, @now_dt) > 15

-- Passing proc_id (rather than proc name) allows to copypaste the debug fragment without changing the proc name each time:
SELECT @proc_name = name FROM sysobjects WHERE id = @proc_id

-- Generate counter which allows to view records in the order they were inserted:
SELECT @counter_for_sort = Max(counter_for_sort) FROM debug_msg WHERE added_by = @user_name
IF @counter_for_sort IS NULL SET @counter_for_sort = 0
SET @counter_for_sort = @counter_for_sort + 1

INSERT debug_msg (counter_for_sort, proc_name, added_dt, added_by, msg) VALUES (@counter_for_sort, @proc_name, @now_dt, @user_name, @dbg_err_msg)

RETURN
User avatar
Ursego
Site Admin
 
Posts: 113
Joined: 19 Feb 2013, 20:33

IF you want to ((lose weight) OR (have unbelievable (brain function AND mental clarity))) THEN click:




cron
free counters

eXTReMe Tracker