I often write multi statement stored procedures and it is very hard to work out what is going on if you don’t debug in SQL Server. Luckily SQL Server management studio allows you to do just this. In this article i am going to explain how to debug in SQL Server, specifically a stored procedure but the same applies for in line functions also. This generally applies to Microsoft SQL Server 2012 on wards but may also apply to earlier versions. Please note the screenshots are from the 2014 Management Studio.
Often I’m calling from a .net environment so to check which exact parameters are being passed in i will often run a SQL profiler. To launch this on SQL server management studio go to Tools>Sql Profiler and then target it at your respective environment.
Then go back to your .net code and run whatever code executes the stored procedure. You should now see the call to your stored procedure in the SQL Profiler. The SQL profiler lists all number of commands that are currently being executed against that environment from every user so it often helps to clear the profiler window using the eraser button at the top just before executing your .net code. When you see the call your expecting in the profiler then copy it from there in to a new query window in SQL Server Management Studio. Now if you hit the “Debug” button at the top of your query window as shown in the image below it should start running the code in debug mode.
If its debugging the code you should see a yellow arrow against the current line of code you are about to execute. To step into your procedure go to the Debug menu at the top and “Step Into” which on my machine has a keyboard shortcut of F11. Please note this keyboard shortcut could be linked to my choice of debug settings in Visual Studio and often there are different settings for VB.net and C#. Once you have stepped into your stored procedure you should be able to see all your declared variables and parameters down at the bottom as per the image below.
You can then run all the way to the end of the procedure which will commit the changes or stop before the end and it will roll it back as if it the code had executed in a transaction.
Unfortunately the only thing lacking with this is being to set parameters in an immediate window like you can do in .net. Maybe this will be a feature in future releases.
Good luck with your debugging! If you would like to see more SQL related articles then head over to our SQL area!
