Is there any way to debug SQL Server 2008 query ?

Question

Hi

is there any way to debug a SQL Server 2008 query ?

Thanks in advance

Answer 1

Yes, you need to set a breakpoint.

Answer 2

Yes. You can use the T-SQL debugger: http://technet.microsoft.com/en-us/library/cc646008.aspx

Here is how you step through a query: http://technet.microsoft.com/en-us/library/cc646018.aspx

Answer 3

What exactly do you mean by debug? Are you seeing incorrect data? Are you getting unexpected duplicate rows?

What I usually do is start with a known set of data, usually one or two rows if possible, and comment out all joins and where conditions.

Introduce each additional element in your query one at a time starting with joins.

At each step, you should know how many records you are expecting.

As soon as you introduce something like a join or where condition that does not match your prediction, you know you have found the problem statement.

If it is a stored procedure with variables and such, you can always PRINT the values of your variables at different points.

If you want to only execute to a particular point in a stored procedure, then you may RETURN at any time and halt processing.

If you have temp tables that must get destroyed between executions while debugging your procedure, a common trick I use is to create a label like-

cleanup:

then at whatever point I want to bail, I can goto cleanup (I know goto is horrible, but it works great when debugging sprocs)

Answer 4

Frankly I find the debugging to be virtually useless. I often don't want to see the variables, but rather the records I would be inserting to a table or updating or deleting.

What I do is this when I havea complex sp to debug.

First I create a test variable. I set it =1 when I want to test. This will ensure that All actions iteh transaction are rolled back at the end (don't want to actually change the datbase until you are sure the proc is doing what you want.) by making sure the commit statement requires the test variable to be set to 0.

At the end of the proc I generally have a if test = 1 begin END statement and between the begin and end, I put the select statments for all the things I want to see the values of. This might include any table variables or temp tables, the records in a particular table after the insert, the records I deleted or whatever else I feel I need to see. If I am testing mulitple times, I might comment out references to tables that I know are right and concentrate only on the ones I've changed that go around. Now I can see what the effect of my proc is and the changes are rolled back in case they weren't right. To actually commit the changes (and not see the intermediate steps) , I simply change the value of the test variable.

If I use dynamic SQL, I also havea debug variable that instead of executing the dynamic SQl simply prints the results out to the screen. I find all this far more useful in debugging a complex script than breakpoints that show me the value of variables.