Solved

How can I debug a stored procedure?

Posted on 2003-11-02
5
9,419 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Embarcadero DBartisan has a facility of debugging SP.

However old and time tested method is PRINT statement.

Cheers,
MAK
0
 

Expert Comment

by:hailieu
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In this step by step tutorial with screenshots, we will show you HOW TO: Enable SSH Remote Access on a VMware vSphere Hypervisor 6.5 (ESXi 6.5). This is important if you need to enable SSH remote access for additional troubleshooting of the ESXi hos…
A procedure for exporting installed hotfix details of remote computers using powershell
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 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

13 Experts available now in Live!

Get 1:1 Help Now