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
    @dbg_user_name varchar(15)   -- always hardcode your user name when calling i_debug_msg; don't use suser_name()!!!
   ,@dbg_proc_id   int           -- pass local var @dbg_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 debugged algorithm is running longer than 15 seconds)
AS
/******************************************************************************************************************************************
INSERTs a debug message INTO debug_msg table.
*******************************************************************************************************************************************
Examples of use (copy the next fragment to a file, change '<Your DB User Name>' and <Your Name>, and use when needed):

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

-- <Your Name> debug code - BEGIN
DECLARE @dbg_user_name char(6), @dbg_err_no int, @dbg_err_msg varchar(500), @dbg_proc_id int, @dbg_rowcount int
SET @dbg_user_name = '<Your DB User Name>', @dbg_proc_id = @@procid
EXEC i_debug_msg @dbg_user_name = @dbg_user_name, @dbg_proc_id = @dbg_proc_id, @dbg_err_msg = 'CALLED'
-- <Your Name> debug code - END

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

------- @@@ To check whether or not the code fragment has been reached:

EXEC i_debug_msg @dbg_user_name = @dbg_user_name, @dbg_proc_id = @dbg_proc_id, @dbg_err_msg = '<<1>>' -- <Your Name> debug code

------- @@@ To check variables' values:

-- <Your Name> debug code - BEGIN
SET @dbg_err_msg = 'Before 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 @dbg_user_name = @dbg_user_name, @dbg_proc_id = @dbg_proc_id, @dbg_err_msg = @dbg_err_msg
-- <Your Name> debug code - END

------- @@@ To check SQL results (@@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 @dbg_user_name = @dbg_user_name, @dbg_proc_id = @dbg_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() = '<Your DB User Name>'":
IF @dbg_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 debugged algorithm is running 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 = @dbg_user_name AND DateDiff(second, added_dt, @now_dt) > 15

-- Passing the value of @@procid (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 = @dbg_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 = @dbg_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, @dbg_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