YT YouTube2Text

YouTube2Text → Transcripts → Debugging sql server stored procedures

Debugging sql server stored procedures — Transcript

by kudvenkat · 2,673 words · 374 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00this is part 107 of SQL Server tutorial
0:03in this video we'll discuss how to debug
0:05store procedures in SQL Server first
0:08let's set up the debugger in SQL Server
0:10management Studio this is the piece of
0:12transact SQL code that we want to debug
0:14notice here we are calling a St
0:15procedure SP print even numbers we have
0:18that stor procedure right here notice
0:20this stor procedure has an input
0:22parameter of type integer if we Supply a
0:25value of 10 to this input parameter then
0:27this to procedure is going to print all
0:29even numbers from 0 till 10 what we want
0:32to do is debug this piece of transact
0:34SQL code and the store procedure that
0:36the SQL code is calling that is sp print
0:39even numbers to start debugging you can
0:42simply click on this debug button or
0:44from the debug menu you can select start
0:47debugging notice we also have the
0:48keyboard shortcut alt
0:50F5 when you start the debugger if the
0:53debugger fails to start and if you see
0:55this error unable to start tsql
0:57debugging could not connect to computer
0:59do
1:00the most likely reason could be that you
1:02might have been using dot or local as
1:05the server name to connect your SQL
1:07Server instance to fix this error all
1:10you have to do is use your computer name
1:12as the server
1:14name so to start debugging you can use
1:17the keyboard shortcut alt F5 or simply
1:20click this button debug this is going to
1:22start the debugger and the line that the
1:24debugger is about to execute is
1:26highlighted in yellow now let's look at
1:28the different options that are ail
1:29available to step through the code while
1:32we are debugging it so we have these
1:34three options step over step into and
1:36step out now you can see these three
1:40options within the debug menu notice we
1:43also have the associated keyboard
1:46shortcuts here now let's understand the
1:48difference between these three options
1:50now at the moment the debugger is
1:52executing this line number two right so
1:55on this line number two whether you
1:57choose step over or step into it doesn't
1:59really matter matter let's choose step
2:01over now the debugger is about to
2:03execute line number three here we are
2:05calling a store procedure s print even
2:07numbers so on this line if we use step
2:11over then the debugger will not provide
2:13us the opportunity to debug the stor
2:15procedure line by line this line will be
2:18executed in its entirety let's look at
2:21that look at this when I select step
2:23over it didn't actually step into
2:25debugging the store procedure line by
2:28line now let's continue
2:31debugging and let's start the debugger
2:34once again and let's choose step over
2:38now on this line instead of using step
2:40over I'm going to select step into so
2:43when we selected step into the debugger
2:45has actually provide us the opportunity
2:47to debug this store procedure line by
2:50line so now I could use either step over
2:53or step into and debug each line of this
2:56store procedure so what is step out then
3:01now we are debugging within the store
3:03procedure now for some reason at this
3:05point I have realized that the problem
3:07is not within the stor procedure so I
3:09want to step out of the stor procedure
3:12that's when we use step out let's
3:14actually select that option and see
3:15what's going to happen look at that the
3:17debugger has finished executing that
3:20store procedure and it's waiting on the
3:22next line that is on line number four
3:24right here so that's what these three
3:27options does step into step over and
3:30step out to stop debugging we can either
3:33use shift F5 or from the debug menu
3:37select stop
3:40debugging show next statement this
3:43command is useful um to show the next
3:45statement that the debugger is about to
3:47execute this is extremely useful when
3:50you have several query editors window
3:52open and you don't know where the
3:53debugger is and the line that the
3:55debugger is about to execute let's
3:57actually look at that in action so let's
3:59start the debug once
4:01again and let's
4:04actually step over and then step into
4:07executing the store procedure now let's
4:09say we have several windows open here
4:11now I don't know where the debugger is
4:13at this point if I want to see what is
4:15the line that the debugger is about to
4:16execute I can simply right click and
4:18then select this option show next
4:19statement now this is going to take me
4:22to the line that the debugger is about
4:23to execute which is very
4:26useful run to cursor this command
4:29execute all the statements in a batch up
4:31to the current cursor position again
4:34very useful let's actually look at that
4:36with an example look at this right here
4:38we are within the St procedure debugging
4:40and we have a while loop here right so I
4:43am debugging the while loop since this
4:46is a loop you know it's going to be
4:49stuck inside this Loop until this
4:50condition becomes false now let's say I
4:52realize that the problem that I'm trying
4:55to solve is not within this Loop so
4:56there's no point wasting in time
4:59executing you know this while loop until
5:01this condition becomes false now I
5:03actually want to see what happens when
5:05this line is executed right so I want
5:09the cursor to run that to that line okay
5:12so what I can do is right click on that
5:14line and then select this option run to
5:16cursor so what is the debug going to do
5:18it's going to execute all this code in
5:22the while loop and then it's going to
5:24come right there so it's going to save a
5:26lot of
5:27time so that's what run to cursor does
5:30locals window this window displays the
5:32current values of variables and
5:34parameters so let's go to SQL so
5:37management Studio here we have the
5:38locals Vendo and look at this at the
5:41moment we are within the context of this
5:43to procedure s print even numbers and if
5:45you look at the store procedure it has
5:47got an input parameter and a local
5:50variable start number so Target um
5:54parameter its name its value and its
5:57data type similarly start number
5:59variable its value its data type so
6:02that's what the locals window does now
6:04let's say you have closed the locals
6:05window now you want to bring it back
6:07it's actually present in debug menu
6:10fendos and
6:13locals watch window this window is
6:16similar to locals window which is used
6:18to watch the values of variables now if
6:20you want to add a variable to a watch
6:23window simply select that variable right
6:25click on that and select add to watch so
6:29this variable is added to the watch
6:30window look at this watch window also
6:32display you know similar data name of
6:35the variable value and its data type now
6:38if you want to add a different variable
6:40to watch window select that and add it
6:43to the watch window okay now if you want
6:46to delete a variable simply right click
6:49on that and select delete Watch if you
6:51want to remove all of the variables
6:54clear
6:57all so watch window very similar to
7:00local window call stack this allows you
7:02to navigate up and down the call stack
7:04to see what values your application is
7:06storing at different levels it's an
7:08invaluable tool for determining what
7:10your code is doing what it's doing at
7:12the moment let's actually understand
7:13this with an example now this is the
7:17main query editor window sqlquery 2. SQL
7:20where we have our transact SQL code
7:22where the debugging started and this
7:24actually called SP print even numbers
7:27and to get your call stack window it's
7:29right right here notice within the call
7:31stack window we have our SQ sqlquery 2.
7:34SQL now if I double click on that it
7:37takes me to the window which called that
7:40stor procs print even numbers look at
7:42that within that window the line which
7:45called our store procedure is
7:46highlighted in green and within our
7:49local window I can actually see you know
7:53at the time when that stor procedure is
7:54called what is the value of this
7:56variable Target number its name it's
7:59variable I mean its value and its data
8:01type and now when we change the context
8:04to the store procedure look at that you
8:07know the local window shows you know the
8:09variables names the values and data
8:11types and here the call stack is showing
8:14us who is calling what what are the
8:16values when those calls are made at the
8:19moment you know we only have two windows
8:21but imagine a situation where you have a
8:23stored procedure calling another stor
8:25procedure that stored procedure calling
8:27another stored procedure and that stor
8:29procedure calling another function so
8:31you want to see who is calling what what
8:33are the values of variables and
8:34parameters at these times you know you
8:37can use call stack which is extremely
8:39useful for debugging and figuring out
8:41what's going
8:43wrong immediate window this is helpful
8:46during debugging to evaluate expressions
8:48and print variable values and to get to
8:51the immediate window it's right here and
8:54we can use this to print variable values
8:56so here we have a variable start number
8:59if I I want to see its value I simply
9:00type it here press enter so start number
9:04value is 10 at the moment I can also
9:07evaluate an expression here so I want to
9:09see what value I get when I divide start
9:11number by two the remainer is zero if
9:15you want to clear the contents of
9:16immediate window simply type angular
9:19bracket followed by CLS and press enter
9:22that's going to clear the immediate
9:23window now let's turn our attention to
9:26break points there are two ways to set
9:27break points in SQL of imag Studio you
9:30can do that either by clicking on the
9:31gray margin on the left hand side in SQL
9:34Server management Studio let's actually
9:37continue and let's start debugging once
9:42again and let's step into executing this
9:44to procedure now I can set a break point
9:47on this line if I want to Simply by
9:49clicking on the gray margin right here
9:52or I can use the keyboard shortcut F9 F9
9:56is actually going to toggle the
9:57breakpoint if the breakpoint is it's
9:59already there it's going to remove that
10:01if it's not there it's going to set that
10:03look at this when I press F9 it toggles
10:06the break point
10:08okay now you also have the option to
10:12enable disable or delete all break
10:15points at once and you can find those
10:18options in the debug menu now let's say
10:20I want to disable all break points that
10:22I have set I can select this option look
10:24at that when I disable a breakpoint
10:25that's how they look like and if I want
10:28to enable them back I can come and
10:29select this option if I want to delete
10:32all the break points we can select
10:33delete all break points and at that
10:35point it's going to throw this uh
10:37confirmation do you want to delete all
10:38break points when you click s it's going
10:40to delete all the break points now let's
10:43look at conditional breakpoint
10:45conditional break points are hit only
10:46when the specified condition is met
10:49these are extremely useful when you have
10:51some kind of a loop and you want to
10:52break only when the loop variable has a
10:55specific value for example when the loop
10:57variable is equal to 100
11:00let's actually understand this
11:01conditional breakpoint because this is
11:02very helpful in debugging all right now
11:05here we have a while loop right and
11:09obviously when I throw in a break point
11:12right here and when I select continue
11:14it's going to be hit right this break
11:17point will be hit every time you now the
11:20code comes to that point so at the
11:23moment start number variable uh has a
11:25value of one now I want to continue
11:29start number variable is two now
11:31actually let's
11:33disable that breakpoint and let's
11:37continue so it finishes and look at this
11:39this store procedure at the moment has
11:41got two problems it doesn't start from
11:43zero and it doesn't print till 10 right
11:46so let's see where the problem is by
11:48debugging it so I'm going to start the
11:52debugger and I'm going to press F10 and
11:54then f11 to step into that store
11:56procedure and I'm going to enable this
11:59uh break point simply by clicking on
12:01that now when I press F10 and when that
12:04break point is hit or when I select alt
12:08F5 you know notice this break point is
12:11hit every time and I don't want that
12:14because you know what is the problem
12:16with the store procedure it's not
12:17printing zero and that could possibly be
12:20because we are starting this counter at
12:22one instead of zero we know that first
12:25problem but the second problem is you
12:28know it could be right yeah because you
12:31know it's printing even numbers until
12:34eight okay and after 8 it's not printing
12:3710 so as long as this variable value is
12:418 it's getting inside this Loop and it's
12:43doing what it is supposed to do but when
12:45it becomes B you know greater than 8
12:48maybe that's when the problem is so I
12:50want this break point to be hit only
12:54when this variable value is greater than
12:568 okay but at the moment since we don't
12:59have a condition on that breakpoint it
13:01will be hit every time okay so to make
13:04this a conditional breakpoint all you
13:06have to do is simply right click on that
13:08breakpoint and select condition and then
13:11you specify your condition here let's
13:13actually copy this variable name and
13:16then I'm going to right click on that
13:17breakpoint select condition throw that
13:20in there and then I'm going to say when
13:24at start number is greater than a value
13:27of eight and look at this is true when
13:29the this condition is true only then hit
13:31that Pro break point so I'm going to
13:33click okay now look at the plus sign so
13:35that's what tells it's a conditional
13:37breakpoint now when we select continue
13:40from here look at the value of the
13:43variable so at the moment the value of
13:46the variable is 9 which means it's
13:48greater than 8 the condition has become
13:50true so that's why this break point is
13:52hit so start number is 9 Target is 10 9
13:57less than 10 true it's going to come
13:59here and start number is 9 when I divide
14:039 by 2 let's see what's going what we
14:06are going to get so I'm going to copy
14:08that expression paste it right there
14:11press enter so we are going to get a
14:12remainer of one so it's not going to go
14:15into that block of code so let's press
14:17F10 so it's going to increment start
14:20number by one so start number is now 10
14:23is 10 less than 10 so when I press F10
14:26there look at that it's not going inside
14:28that l so that's where the problem is
14:30the condition has to be less than or
14:32equal to okay so this demonstrates the
14:35use of conditional breakpoint let's
14:37continue and fix those two errors so I'm
14:41going to initialize this to a value of
14:43zero and I'm going to change the um
14:47condition there to less than or equal to
14:49and let's change this
14:51code okay so we want that to be within
14:55sample DB and now let's execute this
14:59code without debugging and we get the
15:01values from 0 to
15:0510 thank you for listening and have a
15:07great day

About this transcript

This page contains the full transcript of Debugging sql server stored procedures by kudvenkat, generated from the public captions YouTube serves with the video. The transcript has 2,673 words across 374 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.