Debugging sql server stored procedures
HTML-код
- Опубликовано: 6 сен 2024
- 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
In this video we will discuss how to debug stored procedures in SQL Server.
Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our RUclips channel. Hope you can help.
/ @aarvikitchen5572
Setting up the Debugger in SSMS : If you have connected to SQL Server using (local) or . (period), and when you start the debugger you will get the following error
Unable to start T-SQL Debugging. Could not connect to computer.
To fix this error, use the computer name to connect to the SQL Server instead of using (local) or .
For the examples in this video we will be using the following stored procedure.
Create procedure spPrintEvenNumbers
@Target int
as
Begin
Declare @StartNumber int
Set @StartNumber = 1
while(@StartNumber [ @Target)
Begin
If(@StartNumber%2 = 0)
Begin
Print @StartNumber
End
Set @StartNumber = @StartNumber + 1
End
Print 'Finished printing even numbers till ' + RTRIM(@Target)
End
Connect to SQL Server using your computer name, and then execute the above code to create the stored procedure. At this point, open a New Query window. Copy and paste the following T-SQL code to execute the stored procedure.
DECLARE @TargetNumber INT
SET @TargetNumber = 10
EXECUTE spPrintEvenNumbers @TargetNumber
Print 'Done'
Starting the Debugger in SSMS : There are 2 ways to start the debugger
1. In SSMS, click on the Debug Menu and select Start Debugging
2. Use the keyboard shortcut ALT + F5
At this point you should have the debugger running. The line that is about to be executed is marked with an yellow arrow
Step Over, Step into and Step Out in SSMS : You can find the keyboard shortcuts in the Debug menu in SSMS.
Let us understand what Step Over, Step into and Step Out does when debugging the following piece of code
1. There is no difference when you STEP INTO (F11) or STEP OVER (F10) the code on LINE 2
2. On LINE 3, we are calling a Stored Procedure. On this statement if we press F10 (STEP OVER), it won't give us the opportunity to debug the stored procedure code. To be able to debug the stored procedure code you will have to STEP INTO it by pressing F11.
3. If the debugger is in the stored procedure, and you don't want to debug line by line with in that stored procedure, you can STEP OUT of it by pressing SHIFT + F11. When you do this, the debugger completes the execution of the stored procedure and waits on the next line in the main query, i.e on LINE 4 in this example.
To stop debugging : There are 2 ways to stop debugging
1. In SSMS, click on the Debug Menu and select Stop Debugging
2. Use the keyboard shortcut SHIFT + F5
Show Next Statement shows the next statement that the debugger is about to execute.
Run to Cursor command executes all the statements in a batch up to the current cursor position
Locals Window in SSMS : Displays the current values of variables and parameters
If you cannot see the locals window or if you have closed it and if you want to open it, you can do so using the following menu option. Locals window is only available if you are in DEBUG mode.
Watch Window in SSMS : Just like Locals window, Watch window is used to watch the values of variables. You can add and remove variables from the watch window. To add a variable to the Watch Window, right click on the variable and select "Add Watch" option from the context menu.
Call Stack Window in SSMS : 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 why your code is doing what it's doing.
Immediate Window in SSMS : Very helpful during debugging to evaluate expressions, and print variable values. To clear immediate window type ]cls and press enter.
1. From the Debug menu
2. From the Breakpoints window. To view Breakpoints window select Debug =] Windows =] Breakpoints or use the keyboard shortcut ALT + CTRL + B
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 loop varible = 100).
How to set a conditional break point in SSMS :
1. Right click on the Breakpoint and select Condition from the context menu
2. In the Breakpoint window specify the condition
Text version of the video
csharp-video-tu...
Slides
csharp-video-tu...
I watched almost all tutorial. Venkat rules! You are master of SQL Server. Thank U for educating community.
hey Venkat, you are the man. i got a good job because of you. You are the best teacher in the world. Thanks A lot. The way you explaining in a great details, its an Art. YOU ARE SIMPLY THE BEST.
After listening to your lecture, I am very impressed with your knowledge, command on explaining properly & your pronunciation. The best thing is that you are not greedy of money like several other’s annoying popping advertisements. You are really serving not your community but worldwide communities. I am from New York and getting benefitting from your tutorials. At lease get some reasonable number of advertisements that can pay off your time & expenses. Thanks again and keep it up serving humanity.
Hi Venkat Sir..wat a nice explanation...i learned a lot in SQL by watching ur videos, thats helping me a lot in my work as a SQL developer. Thanks for being one of the best invisible teacher with full clarity, u leave me with no doubt in my mind.... Thanks a lot :)
Thank you very much for taking time to give feedback and for all the kind words. This means a lot. I am very glad you found the videos useful.
Dot Net & SQL Server playlists
ruclips.net/user/kudvenkatplaylists?view=1&sort=dd
If you need DVDs or to download all the videos for offline viewing please visit
www.pragimtech.com/kudvenkat_dvd.aspx
Slides and Text Version of the videos can be found on my blog
csharp-video-tutorials.blogspot.com
Tips to effectively use my youtube channel.
ruclips.net/video/y780MwhY70s/видео.html
If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel.
ruclips.net/user/kudvenkat
If you like these videos, please click on the THUMBS UP button below the video.
May I ask you for a favor. I want these tutorials to be helpful for as many people as possible. Please share the link with your friends and family who you think would also benefit from them.
Good Luck
Venkat
Dear Mr. venkat, you are amazing.... very clear and crispy explanations. Loved it.. keep doing . You are really an encouragement to the developer community.
Good Informational video. Clear explanation of every step. Thank you for sharing.
What an excellent use of 15 minutes! You have a marvelous teaching style. thanks so much.
Thanks Mr.Kudvenkat you are helpful man fantastic videos.
Very deep and useful information shares thanks lots
2021:
1 - open visual studio 2019
2 - click esc button or you can create a new project
3 - From View Choose "SQL Server Object Explorer"
4 - right click on Databases Folder Then "Add New Database:
5 - right click on your server then "New Query"
6 - choose your database
7 - write stored procedure code and execute it
8 - write the query to debug
8 - right click any where in query window then "Execute With Debugger" or "Alt + f5"
9 - watch video ...
no its not working
Great Sir
Very good explanation.
Can you let us know what is the use of breakpoint and how does it work?
When should it be used
Sir, I watched almost all of the videos in this video series.. able to get a good level of understanding.. Will you please make a video series for SSIS/SSRS packages and code management using TFS ?
yes really needed ssis and ssrs. plz sir make videos on this
you are very great teacher God bless you my brother
Thank you! That will prove very useful to me.
It's very helpful to debug the usp in MSSQL.
Too much nice explanation.Thanks a Lot.
The best teacher ever
Very Good explanation. Really super, Great Sir...
Extremely useful Venkat. Proud of you.
Thank you Sir. It's very good Explanation. I appreciate.
Thanks very nice demo.... 👌🏻
Beautiful explanation!
Thank you. Can we expect a short explanation for SQL SERVER query execution plan?
Very clear and helpful. But very sadly, MS has removed the debugger in v18 :(
Nicely & succinctly explained.
Thank You Sir to upload this video
this doesnt work anymore in ssms 2018 as the debug menu is gone.
could you solve it? I have you're problem
As of ssms 18 debugger is no longer available, you could use ssms 17 or earlier
Thanks a lot , very short and
to the point
If I can donate money to the author, I will definitely do. This video helps me a lot!
Thankx Man. You are a great man.
very good explanation..
great many thanks
In SQL server 2018 debug option is not available so how can i debug the code
Very helpful. Thank you
Good
Same shortcuts for breakpoints, watch window etc. are also for visual studio.
great videos :)
Thank you Venkat
+kudvenkat Yes, I have already shared your SQL server videos with my friends.
And we have found it very, very useful.
Daily I am watching and learning from your tutorials. :)
Thank you again.
great sir thanks a lot
nice one, thanks a lot
Awesome job! Thank you.
Option not available in SSMS 19
I can't see DEBUG Menu in ssms , how I do that?
Thank You Sir.I have some insert or update statements and that got failed , how can we know from debugger the reason for the failure ?
Thankyou Sir..
Sir i am not getting debug option..i have tried to add it from local security dettings and followed the procedure..but i couldn't get it...plz let me know sir where i was wrong
Dear sir
i am looking for First_Name, Middle_Name and Last_Name textbox value save as FullnName in sql server table and retrieve from table to gridview as fullname. please explain by store procedure.
how to view temp table data which is created in stored procedure While executing the stored procedure in debug mode ?
Thank you!
Hi Venkat. Plz upload the video of execution plan in sql server
According to my knowledge, you're the god of max(developers) Include Dotnet
I have a problem with my procedure, it is misbehaving when it get multiple hits around 2000 in a minute, how can I overcome?
Sir, Can you please upgrade this lesson? VS 2019 to debug SQL Server SP
Please post video on how to populate calender in sql
How do I need to change (localdb)\v11.0 to be able to debug?
I have (localdb)\v11.0
I am trying to change to
(MyCompName)\v11.0
(FullComputerName)\v11.0
MyCompName
FullComputerName
I get error every time. How to fix it?
Thank you
Why do People go to School to learn SQL, They just need to watch Kudvebkats’ Videos! .
Can you help me, how to start a debugger for Azuresql database that connected from local machine.
Dear Sir
Please make video for e-mail trigger.
Once data inserted in customer table i need to send details to my manager by email.
please provide a solution.
is it possible to convert an old asp Web application to asp.net?
no
we can even go to 'Call Stack' window to check which statement is being executed
ruclips.net/video/AlRNA8pmfIk/видео.html
its not divide,
its modular divide