Solved

How can I debug a stored procedure?

Posted on 2003-11-02
5
9,455 Views
Last Modified: 2012-05-04
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?
0
Comment
Question by:happyeveryday
5 Comments
 
LVL 6

Assisted Solution

by:ChrisKing
ChrisKing earned 20 total points
ID: 9671783
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
 
LVL 29

Accepted Solution

by:
leonstryker earned 20 total points
ID: 9673014
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
 
LVL 15

Assisted Solution

by:namasi_navaretnam
namasi_navaretnam earned 20 total points
ID: 9675506
You can also try Sybase debuggers from Embargadaro (Product Name RapidSQL) or Xpeditor

Namasi
0
 
LVL 2

Assisted Solution

by:mansoor_a_khan
mansoor_a_khan earned 20 total points
ID: 9722230
Embarcadero DBartisan has a facility of debugging SP.

However old and time tested method is PRINT statement.

Cheers,
MAK
0
 

Expert Comment

by:hailieu
ID: 9870681
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many Password Managers (PM) out there to choose from. PM's can help with your password habits and routines, but they should not be a crutch you rely on too heavily. I also have an article for company/enterprise PM's.
When we talk about DevOps toolchains, I sometimes wonder how many people really get what we’re talking about. I don’t know if it’s just semantics or tone or something else, but sometimes I think it just sounds like buzzword sausage. So it’s always …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now