Post reply

Checking a SELECT statement when debugging a Stored Proc

  • March 5, 2014 at 2:04 pm

    #304963

    I have a stored proc that says:

    WHILE EXISTS(SELECT * FROM @SystemComponentTbl)

    BEGIN

    It skips that whole section, so I'm assuming the select statement is returning zero.

    When I try to do

    SELECT * FROM @SystemComponentTbl

    I don't see any results in the results window the way I'd typically do.

    So how do I get around this?

    Is doing a count and storing it in a variable the only solution? OR can I see the actual values returned? If so, how if there is more than one row?

    SELECT @CountRows = Count(*) FROM @SystemComponentTbl

    Thanks!

  • March 5, 2014 at 2:14 pm

    #1694493

    EXISTS checks if any result is returned. So if the query would return zero, the condition would succeed (probably not what you want).

    EXISTS returns false if the query doesn't return any row at all. So it is not necesssary to get around it and to use a count.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • March 5, 2014 at 2:30 pm

    #1694497

    Yes, but it not executing. So it is returning FALSE because evidently no rows are returning.

    I want to be able to see what is being returned by the SELECT statements to verify or just to look at what is going on.

    Thanks!

  • March 5, 2014 at 2:35 pm

    #1694499

    Yes, if the SELECT statement returns no rows at all (@SystemComponentTbl is empty in your example), EXISTS will return FALSE and the piece of code will not execute.

    It only makes sense to execute the SELECT statement to check if it actually does return rows.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • March 5, 2014 at 2:54 pm

    #1694504

    Koen Verbeeck (3/5/2014)

    It only makes sense to execute the SELECT statement to check if it actually does return rows.

    That is the reason I came to find out how to see the contents in Debug mode because I don't know how. that is what I'm trying to figure out.

    I"m not sure of the best approach.

  • March 5, 2014 at 3:00 pm

    #1694505

    Well, typically you just run the SELECT statement and see what if returns. If it is a fairly complex statement, you can add TOP 1 to speed it up (EXISTS only needs 1 row to return TRUE).

    Since you are using a table variable, you need to recreate all the steps right before the WHILE loop, and execute all those steps that create the table variable and manipulate data in the variable, including the final SELECT statement.

    To be honest, I don't usually debug SQL scripts using the debugger (I rarely use table variables), so I'm not sure you can see the contents of a table variable while on a breakpoint.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • March 6, 2014 at 11:11 am

    #1694828

    Is the question that hard that no one can answer it?

  • March 6, 2014 at 11:19 am

    #1694830

    1. Go up from your point where you select from @SystemComponentTbl.

    2. Find all occurances where data is inserted, updated or deleted from it.

    3. After each of such query, put: select * from @SystemComponentTbl

    4. After the last such place, put RETURN.

    5. Run it.

    6. From results, you will notice where your data got diappeared.

  • March 6, 2014 at 2:04 pm

    #1694892

    Thanks so I'd have to put this in a separate script? I can't put tests in the stored proc so that when its debugged I can see what is going on?

    This stored proc has over 1000 lines. I was hoping to be able to do something aside from dissecting the whole thing piece by piece.

    I realize that I can pull it a part, I'm just wondering if that is the only way. Or do I misunderstand you?

    Thanks!

  • March 6, 2014 at 2:23 pm

    #1694899

    You don't need to put it in a separate script - just comment create procedure ... as begin,end, etc.

  • March 6, 2014 at 2:24 pm

    #1694900

    Maybe you could insert an extra variable that mirrors your table variable, and insert a copy of whats in the table variable you are testing into the new extra variable, then right before your procedure ends (but after any 'commit'), select * from @extracopyoftablevariable and see if you get anything.

    Really, I've often had to modify my debugging techniques depending entirely on the code I'm debugging, and for that matter, debugging techniques can also be very much language dependent.

    For example, logging tables are great in sql server UNTIL you are logging stuff from within a transaction in action, but at least then you can still log to table variables and then save the contents of those variables to a regular table once you are outside of any transaction block.

    I think its good to use a debugger, but I confess that I've always been more of what called a "print statement" debugger. However, I welcome opinions that think this is a bad thing! http://www.drdobbs.com/architecture-and-design/embedded-print-statements-debugging/240148855

    edit: I do understand that I'm proposing running a modified version of that procedure in question and maybe the OP doesn't want that, just posting 2 cents in case he can modify the procedure being debugged!

  • March 7, 2014 at 2:12 pm

    #1695256

    Ok thanks! I'll try that.

  • March 12, 2014 at 10:39 am

    #1696480

    Thanks!

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply