January 16, 2015 at 8:28 am
Like most of you, I use print/select to debug stored procedures. Sometimes I also use temp tables to track values.
I can be wrong, but I think the SSMS debugger is useless to debug inside of stored procedures.
January 16, 2015 at 8:35 am
Like most other respondents, I make good use of PRINT.
January 16, 2015 at 9:03 am
C#/SQL Server product I work on. Generally start debugging in C# to find the particular stored proc/parameters that are being used.
If after a few minutes I can't figure out where the logic is going back I attach SQL Profiler "snoop" on the request as it hits SQL. I then take the exact request that was run to SSMS and use the debugger from there to step through the stored proc to see what is happening.
If it is something simple you can find it from the profiler too but I find the profiler gets really noisy really quick. I haven't found the magic combination of options that lets me see what lines were run but doesn't show enter and leaving each in between func/stored proc call etc. 1 line of tsql can easily be 10 lines of profiler messages.
January 16, 2015 at 10:13 am
I generally use Print/Select statements but I have found the SQL debugger useful from time to time.
January 18, 2015 at 5:02 pm
Yet another print and temp table guy.
Such a common thing to do I generally have an input parameter in the proc @debug bit = 0 and that way I just make my print statements:
if @debug = 1 print 'whatever it is I want to print'
same can be done writing to the log table.
Seems primitive but it works and I am yet to see anything else that works as clearly.
January 19, 2015 at 5:22 am
Print etc as others but I've fallen in love with CTEs (Common Table Expressions) and use them a lot now, especially to build up complex queries or those with running totals etc.
I can then test each one and then add the next level that refers to it and test that and so on. I think 6 levels, each referring to the previous (and more than one cte at that level), is my best so far for a complex inventory valuation running total query.
January 19, 2015 at 7:38 am
GilaMonster (1/16/2015)
call.copse (1/16/2015)
I'm still a PRINT and temp table guy I'm afraid :blush: feels quite stone age but you know where you stand.Stone age, but it works. I'm there too. Print, raiserror (where necessary), temp tables/table variables, stats IO/Time and execution plans
+1 It works and I find any debugging tool I've used in the past to also be flaky and cumbersome.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 19, 2015 at 8:50 am
ShineBoy (1/18/2015)
Yet another print and temp table guy.Such a common thing to do I generally have an input parameter in the proc @debug bit = 0 and that way I just make my print statements:
if @debug = 1 print 'whatever it is I want to print'
same can be done writing to the log table.
Seems primitive but it works and I am yet to see anything else that works as clearly.
It is primitive and it's the technique that I've used for I don't know how many years. Good on you!
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
January 19, 2015 at 9:03 am
I'm a PRINT statement, temp-table kind of guy.
January 20, 2015 at 4:53 am
I always thought that I was just ignorant of whatever technologically advanced solution everyone else uses for debugging, with my use of PRINT statements, but it looks like I'm not (in this instance!).
February 10, 2015 at 12:21 am
I use a variety of techniques stipulated here including adding PRINT statements (majority), raising errors, SQL Debugger, Profiler, viewing the execution plan.
If it is just SELECT statements then sometimes I also copy the statements into a script window and execute each statement in isolation.
Of course, this is only when it isn't covered by my unit tests 😉
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
May 27, 2019 at 10:50 am
When looking at why software isn't working correctly I first use SQL Profiler to see what queries/procedures are being called when I press something and then use PRINT within them if SQL is dynamic. If a server is busy, which they always seem to be, I add in some additional columns such as host name and user name to be able to find the commands I have triggered.
When SQL gets over 4,000 chars then I need to use this instead as PRINT truncates the output:
SELECT @SQLString AS [processing-instruction(x)] FOR XML PATH('')
Sometimes comments within the SQL can confuse this though but it seems the best approach I can find.
May 27, 2019 at 12:54 pm
In SSMS I find it useful to print or SELECT the variable as the code runs and see how the values change.
In SSIS I find it useful to use MessageBox.Show() method to display variable values.
May 27, 2019 at 2:24 pm
Sanjarani, I found the same regarding debugging tools. I could do a better job of debugging by embedding my own selects, prints, and using temporary tables for intermediate results. These were then easily removed or commented when the debugging was completed. This way I could see exactly what I needed when and where I needed it.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
May 27, 2019 at 2:28 pm
Print etc as others but I've fallen in love with CTEs (Common Table Expressions) and use them a lot now, especially to build up complex queries or those with running totals etc. I can then test each one and then add the next level that refers to it and test that and so on. I think 6 levels, each referring to the previous (and more than one cte at that level), is my best so far for a complex inventory valuation running total query.
Obviously works for you, but sounds like a maintenance nightmare to me! 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply