[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

debug stored procedure

Can you debug a stored procedure using SQL Server 7? If so, how. I don't see a debug feature on any menus but I thought I heard somewhere that you can.
0
stacey14
Asked:
stacey14
  • 3
1 Solution
 
chickenfrankCommented:
Add print or select statements to your stored procedure and execute the stored procedure from query analyzer.

for example:  
print @variable

OR  
select @variable AS 'value of @variable after the insert'


For me, this is the easiest way to debug.  Not very elegant, but it doesn't require installing anything.  Its perhaps a little more tedious.  Even if you use a debugginf utility, you still have to set up breakpoints, which also requires work.

If you're debugging an application, you can run sql profiler to capture how the stored procedure is being called by the application.  copy and paste into query analyzer and hit run.  look for what your print statements print out.
0
 
AycexCommented:
Depends on exactly what you are looking for.  I know some people like to know exactly where or at least narrow it down to a couple of lines.  In that case use




Selsct *

select 10

FROM table_1

select 20


WHERE product_id = 2342346864

Select 30
0
 
AycexCommented:
Depends on exactly what you are looking for.  I know some people like to know exactly where or at least narrow it down to a couple of lines.  In that case use




Selsct *

select 10

FROM table_1

select 20


WHERE product_id = 2342346864

Select 30
0
 
JohnChapinCommented:
This problem drove me crazy until i gave up and created a debug table. I use this code and repeat inserts as necessary.  
a_JCC_rc is a four column table
prime-key
varchar
date
int





DECLARE  @retrows int
DECLARE  @DEBUGLOG int
DECLARE @TimeStamp datetime
set @TimeStamp = getdate()                 -- now
--set @DEBUGLOG = 0               -- for production
set @DEBUGLOG = 1               -- for test

set nocount on --To allow only last select to return rows from execution, using a cursor kills this attempt


if (@DEBUGLOG = 1) Truncate table  a_JCC_rc     --reset counters for new test
set @retrows = @@rowcount
if (@DEBUGLOG = 1) insert into a_JCC_rc  values ('----------db ' + @DataBaseName, getdate(), @retrows )

-- Select statement in development

set @retrows = @@rowcount
if (@DEBUGLOG = 1) insert into a_JCC_rc  values ('1- Select identifier ', @TimeStamp, @retrows )
if (@DEBUGLOG = 1) insert into a_JCC_rc  (Returned_rows_label)    select distinct UserLogin from #temp


select * from a_jcc_rc
0
 
AycexCommented:
Depends on exactly what you are looking for.  I know some people like to know exactly where or at least narrow it down to a couple of lines.  In that case use




Selsct *

select 10

FROM table_1

select 20


WHERE product_id = 2342346864

Select 30
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now