One feature I have never used in SQL Server was debugging and stepping through my SQL code. I’ve clicked in the left margin of a query window and accidentally created a breakpoint, but that was the extent of my experience.
It turns out to be pretty easy to do and could be very helpful in debugging queries and stored procedures.
I put together a script(here on Github) that creates a table and a stored procedure that inserts a row into the table and then selects that row.
After creating the table and proc, I open a second query window and add the code to call the stored procedure:
declare @TeamName char(3) = 'ATL'; declare @JerseyNumber tinyint = 2; declare @FirstName varchar(25) = 'Matt'; declare @LastName varchar(25) = 'Ryan' declare @Position char(2) = 'QB'; exec dbo.InsertPlayer @TeamName, @JerseyNumber, @FirstName, @LastName, @Position;
You click the ‘Debug’ button on the toolbar (or alt + F5) to start debugging. F10 will step line by line through the script, so we can step down to the line where the proc is called. As we step through, we can see a Locals window which will give us the values of the variables in scope. In this case, we’re just setting the values once, but this could be valuable in a case where the values are updated and may get set to a value that we aren’t expecting. Once we’re down to the proc call (the current line is highlighted in yellow), hitting F11 will step into the stored procedure. We’ll see in the Call Stack window that a second line appears, to show that we have jumped from the proc call down into the proc code. This will open a new window with the stored procedure code. We can now step line by line through the proc code, in this case through the insert and then to the select. Once we get to the end of the proc code and step again, we’ll jump back to the calling window, and we’ll see the results of the proc.
We can also set breakpoints in the code by clicking to the left of a line in the query window. Hitting Alt+F5 will run each line up to the breakpoint and pause execution.
I worked through this example on a local SQL Server 2014 Developer instance. There would be a little more work to do to set up debugging on a remote server, but the same concepts still apply.
MSDN – Bill Ramos