Debugging tips

[meny21]

Hi guys,

I used to work with SP's since few months, but I would like to improve myself with SQL Server Managment Studio 2005.

Do you know guys tips about how to debug SP's with this tool? I mean something that you use when this is needed or maybe a good tip that you find on this tool that you think will help us, maybe something basic for new people on this like me ;O)

Thanks a lot!

MR

 

[TysonLPrice]

They took away the debugging tool that could be used from Query Analyzer in 2000. If you have the Visual Studio 2005 development environment you can step through them there. I tend to use:

1. Print statements sprinkled at key points.
2. I create a temporary table and insert variable vales and debugging statements in it at key points. Then review them at the end of the run.

 

[SQLSister]

Personally I set up any complex proc with a test variable as an input varaible. If it is set to 1 it runs in test mode which then displays key information at certain points, displays the total end result of what the data in the real tables looks like at the end of the proc and then rolls back everything (we work in transactions for procs that insert, update or delete in multiple steps).

I also never write an insert, update or delete that doesn't have a corresponding select (well, except in the very rare cases when I use a values clause) to tell me what will be affected. These are what I display in my test mode.

Most developers I know who write dynamic SQL also have a debug variable as an input variable so that they can just print the code the dynamic query will create rather than running it.

"NOTHING is more important in a database than integrity." ESquared

 

[markros]

You can debug SP in SQL Server 2008 or from Visual Studio with SQL Server 2005.