How can I debug a stored procedure?

Help!   I wrote a stored procedure as the following,  I want to debug it, but I didn't know how to do. I have ever tried to show all the local variables of the procedure, but when I use "print" command , it doesn't work.  The  sybase is of version 11.1.
By the way, the following is for the functionality of "Pagely query" , if possible, please diagnose it and instruct me where it is wrong. Thank you.

create procedure GetRecordsOfCurrentPage(
       @PageSize int,
       @PageNO int,
       )
as
begin
    declare @aa int
    declare @bb int
    declare @totalcount int
    declare @temp varchar(18)
    if @PageNO >= 1
              begin
                  declare cur cursor for select count(*) from user1.table1 tb where tb.name = 'TOM'
                  open cur
                  fetch cur into @totalcount
                  close cur
                  select @aa = (@PageNO - 1) * @PageSize + 1        
                  select @bb = (@PageNO - 1) * @PageSize + @PageSize
                  if @totalcount < @bb
                     select @bb = @totalcount
                  declare @StartRec varchar(18), @EndRec varchar(18), @Str1 nvarchar(200)
                  declare @i int, @j int
                  declare cur1 cursor for select tb.Key1 from user1.table1 tb
                      where tb.name = 'TOM' order by tb.Key1 asc for read only
                  open cur1
                  select @i = 1
                  while @i < @aa
                      begin
                           fetch cur1 into @temp
                           select @i = @i + 1
                      end
                  if @i = @aa
                      begin
                           fetch cur1 into @StartRec
                      end
                  select @j = @i
                  while @j < @bb
                      begin
                          fetch cur1 into @temp
                          select @j = @j + 1
                      end
                  if @j = @bb
                      begin  
                          fetch cur1 into @EndRec
                      end
                  close cur1              
                  set rowcount @PageSize
                  select * from user1.table1 tb where tb.name = 'TOM' and tb.Key1 between @StartRec and @EndRec
                  set rowcount 0
           end
      end
  end

I have known there is 65 records with name of TOM, now when I run
"exec GetRecordsOfCurrentPage 40,1"
it get the correct results
but when I run
"exec GetRecordsOfCurrentPage 40,2"
it show no record, but the expectation should be 25 records showed.
I was puzzled , and more badly I don't know how to dedug it.  can you help me?
happyeverydayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ChrisKingCommented:
in short, in the absence of a program like "sp debugger", add print (or select) statements into your store procedure.

but, your use of cursors will just slow you down

for example, replace:
                  declare cur cursor for select count(*) from user1.table1 tb where tb.name = 'TOM'
                  open cur
                  fetch cur into @totalcount
                  close cur
with:
                  select @totalcount = count(*) from user1.table1 tb where tb.name = 'TOM'

you would be much better off using a temp table for the this procedure.
eg.

create procedure GetRecordsOfCurrentPage
(   @PageSize int
,   @PageNO int
)
as
    declare @firstrow int, @lastrow int

    select @lastrow = @PageNO * @PageSize
    select @firstrow = @lastrow + 1 - @PageSize

    create table #yourrows ( rownum numeric(9,0) identity, Key1 varchar(18) )
    insert into #yourrows ( Key1 )
    select Key1 from table1 where name = 'TOM' order by Key1

    select t1.* from #yourrows yr, table1 t1 where yr.rownum between @firstrow and @lastrow and t1.Key1 = yr.Key1

    truncate table #yourrows
    drop table #yourrows

    return
0
leonstrykerCommented:
As ChrisKing pointed out temp tables are the way to go.  In addition they are helpful for debuggings since it is rather easy fo ryou to run a select statement from the temp table at any point so as to see the data contained there.  

A good trick I have learned to use for debuging is to create an additional variable for the store proc and default it to null then in the code of your procedure you can print out the variables and/or temp tables or the actual result of your procedure based on that variable.  For example

create procedure GetRecordsOfCurrentPage
(   @PageSize int
,   @PageNO int
.   @debug int = NULL
)
as

-- Your code

IF @debug IS NULL
    BEGIN
--        Your results as normal
    END
ELSE
    BEGIN
        SELECT @PageSize, @PageNO
        SELECT * FROM #temp1
    END
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
namasi_navaretnamCommented:
You can also try Sybase debuggers from Embargadaro (Product Name RapidSQL) or Xpeditor

Namasi
0
mansoor_a_khanCommented:
Embarcadero DBartisan has a facility of debugging SP.

However old and time tested method is PRINT statement.

Cheers,
MAK
0
hailieuCommented:
happyEveryDay,

The simplest thing I should do without change any code is use select command because the print command is only showing up on the status window.

Here is an example from your code:

create procedure GetRecordsOfCurrentPage(
       @PageSize int,
       @PageNO int,
       )
as
begin
    declare @aa int
    declare @bb int
    declare @totalcount int
    declare @temp varchar(18)
select "I am start go through step by step to debug where I want and know exactly where I am"
    if @PageNO >= 1
              begin
                  declare cur cursor for select count(*) from user1.table1 tb where tb.name = 'TOM'
                  open cur
                  fetch cur into @totalcount
                  close cur
                  select @aa = (@PageNO - 1) * @PageSize + 1        
                  select @bb = (@PageNO - 1) * @PageSize + @PageSize
select "The totalcount is "+@totalcount...."+" The @bb is "+@bb
                  if @totalcount < @bb
                     select @bb = @totalcount
select
                  declare @StartRec varchar(18), @EndRec varchar(18), @Str1 nvarchar(200)
                  declare @i int, @j int
                  declare cur1 cursor for select tb.Key1 from user1.table1 tb
                      where tb.name = 'TOM' order by tb.Key1 asc for read only
                  open cur1
                  select @i = 1
                  while @i < @aa
                      begin
                           fetch cur1 into @temp
                           select @i = @i + 1
                      end
                  if @i = @aa
                      begin
                           fetch cur1 into @StartRec
                      end
                  select @j = @i
                  while @j < @bb
                      begin
                          fetch cur1 into @temp
                          select @j = @j + 1
select "....... Increase the @j to 1 now is "+@j
                      end
                  if @j = @bb
                      begin  
                          fetch cur1 into @EndRec
                      end
                  close cur1              
                  set rowcount @PageSize
select "............I set rowcount to "+@PageSize+"  ...and the following is the result"
                  select * from user1.table1 tb where tb.name = 'TOM' and tb.Key1 between @StartRec and @EndRec
                  set rowcount 0
select "..............I reset rowcount to 0"
           end
      end
select "This is only the sample ! You know the idea!"
  end
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.