Solved

How can I debug a stored procedure?

Posted on 2003-11-02
5
9,498 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Data source name not found , no default driver specified. ODBC, Excel and SqlAnywhere 3 2,589
ORCA Powerbuilder import 7 1,122
sybase T-sql different 2 116
Why did SAP buy Sybase? 3 168
This article describes my battle tested process for setting up delegation. I use this process anywhere that I need to setup delegation. In the article I will show how it applies to Active Directory
This article explains the steps required to use the default Photos screensaver to display branding/corporate images
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

809 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