Debugging Tips and Help

Hi. I need some help or guidance for debugging stored procedures and
fuctions. I would say I have intermediate knowledge/skills but wanted
to know if anyone has any suggestions on how I can improve my skills.
I know that QA is the tool to use and like I said I do have knowledge
as I have a programming background but since I am new to sprocs I need
all the help I can get. :) So any and all assistance will be greatly
appreciated.
JulesHi
Once you have identified that the stored procedure is disfunctional, the
first step is usually to find out what parameters are being passed. The
easiest way to do that is to use SQL Profiler. Once you know the values to
put into the parameters, you can start QA. You may then want to paste the
stored procedure call from SQL profiler into a new windows and run it
"manually". Depending on whether you can easily spot the line of code causin
g
the problem or not (the error message may give a line number or some
information that allows you to track down what is wrong) you may find that
you want to step through the stored procedure and see more how the code is
traversed.
To do this you can run the debugger, by opening up the object browser window
(F8) and find the procedure required. Then right click the procedure and
choose debug from the menu. This will give you a dialog where you can put in
the values for the parameters, you then choose execute and the debugger
should stop at the first line of code inside the procedure. There are then
buttons to allow you to step over/step next etc..
HTH
John
"Jules" wrote:

> Hi. I need some help or guidance for debugging stored procedures and
> fuctions. I would say I have intermediate knowledge/skills but wanted
> to know if anyone has any suggestions on how I can improve my skills.
> I know that QA is the tool to use and like I said I do have knowledge
> as I have a programming background but since I am new to sprocs I need
> all the help I can get. :) So any and all assistance will be greatly
> appreciated.
> Jules
>|||John,
Thank you for helping me out and I will use your help as I think it
will be EXTREMELY beneficial to and for me!
Jules|||Hi
Glad to hear that, remember that books online is an extreemly useful source
of information. It is quite often worth browsing through and reading when yo
u
have a spare moment. If you don't have the latest version they can be
downloaded from
http://www.microsoft.com/sql/techin...000/books.mspx.
SQL Profiler is possibly is also a wonderful gem and quite often under used.
It is worth checking the different things you can log and what they mean. Yo
u
may want to see Tony's blog casts on http://www.sqlserverfaq.com/ for an
introduction to SQL Profiler. There is also one on the Index Tuning wizard..
.
John
"Jules" wrote:

> John,
> Thank you for helping me out and I will use your help as I think it
> will be EXTREMELY beneficial to and for me!
> Jules
>