Debugging sql server stored procedures

this is part one zero seven of sequel server tutorial in this video we'll discuss how to debug stored procedures in sequel server first let's set up the debugger in sequel server management studio this is the piece of transaxial code that we want to debug notice here we are calling a stored procedure SP print even numbers we have that stored procedure right here notice the stored procedure has an input parameter of type integer if we supply a value of 10 to this input parameter then this store procedure is going to print all even numbers starting from 0 till 10 what we want to do is debug this piece of transaxial code and the stored procedure that the sequel code is calling that is SP print even numbers to start debugging you can simply click on this debug button or from the debug menu you can select start debugging notice we also have the keyboard shortcut all the Fi when you start the debugger if the debugger fails to start and if you see this error unable to start T sequel debugging could not connect to computer dot the most likely reason could be that you might have been using dot or local as the server name to connect your sequel server instance to fix this error all you have to do is use your computer name as the server name so to start debugging you can use the keyboard shortcut alt f5 or simply click this button debug this is going to start the debugger and the line that the debugger is about executes is highlighted in yellow now let's look at the different options that are available to step through the code while we are debugging it so we have these three options step over step into and step out now you can see these three options within the debug menu notice we also have the Associated keyboard shortcuts here now let's understand the difference between these three options now at the moment the debugger is executing this line number two right so on this line number two whether you choose step over or step into it doesn't really matter let's choose step over now the debugger is about executed line number three here we are calling a stored procedure as we print even numbers so on this line if we use step over then the debugger will not provide the opportunity to debug the stored procedure line by line this line will be executed in its entirety let's look at that look at this when I select step over it didn't actually step into debugging the stored procedure line by line now let's continue debugging and let's start the debugger once again and let's choose step over now on this line instead of using step over I'm going to select step n too so when we selected step into the debugger has actually provided as the opportunity to debug the stored procedure line by line so now I could use either step over up step into and debug each line of this stored procedure so what is step out then now we are debugging within the stored procedure now for some reason at this point I have realized that the problem is not within the stored procedure so I want to step out of the stored procedure that's when we use step out let's actually select that option and see what's going to happen look at that the debugger has finished executing that stored procedure and it's waiting on the next line that is on line number four right here so that's what these three options does step into step over and step out to stop debugging we can either use shift f5 or from the debug menu select stop debugging show next statement this command is useful to show the next statement that the debugger is about to execute this is extremely useful when you have several query editors window open and you don't know where the debugger is and the line that the debugger is about to execute let's actually look at that in action so let's start the debugger once again and let's actually step over and then step into executing the stored procedure now let's say we have several windows open here and now I don't know where the debugger is at this point if I want to see what is the line that the debugger is about to execute I can simply right click and then select this option show next statement now this is going to take me to the line that the debugger is about to execute which is very useful learn to cursor this command executes all the statements in a batch up to the current cursor position again very useful let's actually look at that with an example look at this right here we are within the stored procedure debugging and we have a while loop here right so I am debugging the while loop since this is a loop you know it's going to be stuck inside this loop until this condition becomes false now let's say I realize that the problem that I am trying to solve is not within this loop so there's no point wasting in time executing you know this while loop until this condition becomes false now I actually want to see what happens when this line is executed right so I want the cursor to run that to that line okay so what I can do is right-click on that line and then select this option run to cursor so what is the debug going to do it's going to execute all this code in the while loop and then it's going to come right there so it's going to save a lot of time so that's what run to cursor does locals window this window displays the current values of variables and parameters so let's go to sequencer management studio here we have the locals window and look at this at the moment we are within the context of the stored procedure has people int even numbers and if you look at the stored procedure it has got an input parameter and a local variable start number so target parameter its name its value and its data type similarly start number variable its value it's data type so that's what the locals vendor does now let's say you have closed the locals window now you want to bring it back it's actually present in debug menu vendors and locals watch window this window is similar to locals window which is used to watch the values of variables now if you want to add a variable to a watch window simply select that variable right click on that and select add to watch so this variable is added to the watch window look at this watch window also display in a similar data name of the variable value and its data type now if you want to add a different variable to watch window select that and add it to the watch vendor okay now if you want to delete a variable simply right-click on that and select delete watch if you want to remove all of the variables clear all so watch window very similar to locals window call stack this allows you to navigate up and down the call stack to see what values your application is storing at different levels it's an invaluable tool for determining what your code is doing what it's doing at the moment let's actually understand this with an example now this is the main query editor window sequel query to dot sequel where we have our transact sequel code where the debugging started and this actually called SP print even numbers and to get you a call stack window it's right here notice within the call stack window we have our SQL SQL query 2 dot sequel now if I double click on that it takes me to the vendor which called that stored procedures P print even numbers look at that within that window the line which called our stored procedure is highlighted in green and within our locals window I can actually see you know at the time when that stored procedure is called what is the value of this variable target number its name its variable I mean its value and its data type and now when we change the context to the stored procedure look at that in other locals window shows you know the variables names the values and data types and here the call stack is showing us who is calling what what are their values when those calls are made at the moment you know we only have two windows but imagine a situation where you have a stored procedure calling another store procedure that stored procedure calling another stored procedure and that's tripsy a calling another function so you want to see who is calling what what are the values of variables and parameters at these times you know you can use call stack which is extremely useful for debugging and figuring out what's going wrong immediate window this is helpful during debugging to evaluate expressions and print variable values and to get to the immediate window it's right here and we can use this to print variable values so here we have a variable start number if I want to see its value I simply type it here press Enter so start number value is 10 at the moment I can also evaluate an expression here so I want to see what value I get when I divide start number by 2 the reminder is 0 if you want to clear the contents of immediate window simply type angular bracket followed by CLS and press Enter that's going to clear the immediate render now let's turn our attention to break points there are two ways to set breakpoints in sequence level management studio you can do that either by clicking on the gray margin on the left hand side in sequel server management studio let's actually continue and let's start debugging once again and let's step into executing this tour procedure now I can set a breakpoint on this line if I want to simply by clicking on the gray margin right here or I can use the keyboard shortcut f9 f9 is actually going to toggle the break point if the breakpoint is already there it's going to remove that if it's not there it's going to set that look at this when I + f9 it toggles the breakpoint ok now you also have the option to enable disable or delete all breakpoints at once and you can find those options in the debug menu now let's say I want to disable all breakpoints that I have set I can select this option look at that when I disable a breakpoint that's how they look like and if I want to enable them back I can come and select this option if I want to delete all the breakpoints we can select delete all breakpoints and at that point it's going to through this confirmation do you want to delete all breakpoints when you click S it's going to delete all the breakpoints now let's look at conditional breakpoint conditional breakpoints are hit only when the specified condition is met these are extremely useful when you have some kind of a loop and you want to break only when the loop variable has a specific value for example when the loop variable is equal to 100 let's actually understand this conditional breakpoint because this is very helpful and debugging all right now here we have a a loop right and obviously when I throw in a breakpoint right here and when I select continue it's going to be hit right this breakpoint will be hit every time and now the code comes to that point so at the moment start number variable has a value of 1 now I want to continue start number variable is 2 now actually let's disable that breakpoint and let's continue so it finishes and look at this this tour procedure at the moment has got two problems it doesn't start from 0 and it doesn't print till 10 right so let's see where the problem is by debugging it so I'm going to start the debugger and I'm going to press f10 and then f11 to step into that stored procedure and I'm going to enable this breakpoint simply by clicking on that now when I + f10 and when that breakpoint is hit or when I select alt f5 you know notice this breakpoint is hit every time and I don't want that because you know what is the problem with the stored procedure it's not printing 0 and that could possibly be because we are starting this counter at 1 instead of 0 we know that first problem but the second problem is you know it could be right here because you know it's printing even numbers until 8 okay and after 8 it's not printing 10 so as long as this variable value is 8 it's getting inside this loop and it's doing what it is supposed to do but when it becomes B you know greater than 8 maybe that's when the problem is so I want this breakpoint to be hit only when this variable value is greater than 8 okay but at the moment since we don't have a condition on that breakpoint it would be hit every time okay so to make this a conditional breakpoint all you have to do is simply right click on that breakpoint and select condition and then you specify your condition here let's actually copy this variable name and then I'm going to right click on that breakpoint select condition throw that in there and then I'm going to say when at start number is greater than a value of 8 and look at this is true when this condition is true only then hit that break point so I'm going to click OK now look at the plus sign so that's what tells it's a conditional breakpoint now when we select continue from here look at the value of the variable so at the moment the value of the variable is 9 which means it's greater than 8 the condition has become true so that's why this breakpoint is hit so start number is 9 target is 10 9 less than 10 true it's going to come here and start number is 9 when I divide 9 by 2 let's see what's going what we are going to get so I'm going to copy that expression paste it right there press Enter so we are going to get a remainder of 1 so it's not going to go into that block of code so let's press f10 so it's going to increment start number by 1 so start number is now 10 is 10 less than 10 so when I press f10 there look at that it's not going inside that loop so that's where the problem is the condition has to be less than or equal to okay so this demonstrates the use of conditional breakpoint let's continue and fix those two errors so I'm going to initialize this to a value of 0 and I'm going to change the condition there to less than or equal to and let's change this code okay so we want that to be within sample dB and now let's execute this code without debugging and we get the values from 0 to 10 thank you for listening and have a great day

Channel: kudvenkat

Views: 183,556

Rating: 4.8975611 out of 5

Keywords: debugging in ssms, debugging t-sql code, t sql debug stored procedure, sql server management studio debug stored procedure, how to debug t sql, debug in sql server management studio

Id: AlRNA8pmfIk

Length: 15min 11sec (911 seconds)

Published: Mon Sep 28 2015