Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How can I debug a stored procedure?

Posted on 2003-11-02
5
Medium Priority
?
9,642 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 6

Assisted Solution

by:ChrisKing
ChrisKing earned 60 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 60 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 60 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 60 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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

With the evolution of technology, we have finally reached a point where it is possible to have home automation features like having your thermostat turn up and door lock itself when you leave, as well as a complete home security system. This is a st…
It’s time for spooky stories and consuming way too much sugar, including the many treats we’ve whipped for you in the world of tech. Check it out!
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

598 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