Executing SELECT statement in Debug Mode

  • Jacob Pressures

    SSCertifiable

    Points: 5804

    Hi Guys,

    I notice when i execute a SELECT statement in debug mode, i don't get the list of query results? Is there a way to make the results show in the result window or whatever?

    Thanks!

  • as1981

    SSCrazy

    Points: 2761

    Which software are you using?

  • Jacob Pressures

    SSCertifiable

    Points: 5804

    I have two. I mostly work with v17.9 and ocasionally v18.5.

     

    Thanks!

  • Jacob Pressures

    SSCertifiable

    Points: 5804

    I see this article that the debugger is deprecated in v18.

    https://dba.stackexchange.com/questions/221349/how-to-add-the-debug-button-to-ssms-v18#:~:text=2%20Answers&text=Debugger%20is%20deprecated%20in%20SSMS18,Studio%20to%20debug%20a%20query.&text=This%20feature%20is%20deprecated%20in%20SSMS%2018%20and%20thus%20no%20longer%20available.

    What do you guys use data tools? Is there a viable alternative? Or should i just use debugger or do something else?

  • Erland Sommarskog

    SSC-Insane

    Points: 23929

    It's over 15 years since I gave up using an SQL debugger. I found that I spent more time to get to work than I actually got out of it. PRINT and SELECT works well. Occasionally I use Trace.

    Overall, a debugger for SQL is not equally interesting as it is for client-side languages where you have use for single-stepping through the code and inspect variables. But in SQL you work with sets of data, and you need to look at the tables. Which, as I recall, you can't inspect from the debugger.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Jacob Pressures

    SSCertifiable

    Points: 5804

    Thanks Erland,

    My problem is that my code has lots of variables. I feel i have to replace my variables with the actual value. That gets messy over time. If i tweak my code and find that it is now working, then i have to put the variables back in. In theory the code should be tested again to make sure the results are the same after i altered it after adding the variables again.

    I think at some point ppl like the piece of mind of knowing this code is working. since my last few tests its not been tweaked at all.

    Selecting from the top  of the declaration statements all the way down a long stored proc is also annoying after a while.

    I'm assuming though, that this is the only alternative.

  • Erland Sommarskog

    SSC-Insane

    Points: 23929

    I don't see why the variables would be a problem. They can be dumped with PRINT and SELECT as well. No reason to replace them with something else to debug.

     

    (And "lots of variables" makes me believe that you are writing loops. Which you don't do that often in SQL. Or at least you shouldn't.)

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Jeffrey Williams

    SSC Guru

    Points: 88652

    Erland Sommarskog wrote:

    I don't see why the variables would be a problem. They can be dumped with PRINT and SELECT as well. No reason to replace them with something else to debug.

    (And "lots of variables" makes me believe that you are writing loops. Which you don't do that often in SQL. Or at least you shouldn't.)

    It doesn't necessarily mean a loop - but I would agree that a 'lot' of variables may indicate an issue with the procedure and/or process.  It could be a monolithic procedure that steps through a business process that might be better written as separate procedures or functions.

    Really couldn't say for sure....but it does make me think there probably is a better approach.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jacob Pressures

    SSCertifiable

    Points: 5804

    One the of procs have a loop. One of them just steps through processes.

    We are deactivating items and checking criteria for item deactivation.

    The issue isn't knowing that the value are. The issue is that I have to start the query from scratch and/or from the top all the way down the the next level i want to evaluate. It seems like a lot of work just to re-execute the next line of code.

    But i guess i could just run it and check multiple select statements at once. Perhaps that is the way to do it rather than one-by-one the way i'm used to doing it.

    What do you think? Thanks guys!

Viewing 9 posts - 1 through 9 (of 9 total)

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