Debugging sql server stored procedures Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

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