Monday, November 18 2013
Keywords: Debug, Stored Procedure, SQL Server, Visual Studio 2013, VS 2013, T-SQL, TSQL
Debug SQL Server 2012 Stored Procedure in Visual Studio 2013, Step by Step
Download the source code pertaining to this project, which could be found on the bottom of this page. Prior to unzipping the content of the file you must Right-Click on the file --> Properties --> Unblock, otherwise Windows will not trust the Dlls in the debug folder and it will bypass the PL/SQL code as you try to step into it.
Please note that my sample example is of "Website" project type...you will notice that in the zip file I have two sub-folders. You need to copy the content of "Projects" sub-folder in my zip file to your Visual Studio "Projects" sub-folder, and then do the same for "Websites".
And finally run SQL Server Management Studio 2012 (SSMS), open “Debug from VS 2013.sql” and Execute it to create the DatabaseFOO database. Afterwards you could close SSMS. You won’t need it for debugging.
2) Run Visual Studio 2013 and open “DebugStoredProcsProj.sln”. Do not run the project yet.
3) Right click on the project (and not the solution) and then select Properties. Click pn the "Web" tab and make sure SQL Server is selected in the list of debuggers. Also note that in this example I am using IIS Express, but you could be using the full IIS as well, it won't make any difference. Save the project and close the properties settings page.
4) Back to VS 2013. Open up “Server Explorer”, usually placed on the left hand side of the screen.
5) Click on SQL Server Object Explorer
6) If you do not see your SQL Server listed here already, then click on “Add SQL Server”
7) Right-click on your SQL Server and make sure Application Debugging is selected, otherwise the attachment won’t automatically occur and you will not be able to step into T-SQL. You do not need to have “Allow SQL/CLR Debugging” selected, that would be to debug T-SQL code that calls CLR.
8) Click on the little arrow on the left of your SQL Server, and find DatabaseFOO database, and navigate down until you see the three stored procedures.
9) Double-click on DeleteAllFOO stored procedure. A window will open in the active main area displaying the stored procedure’s script body. Place a breakpoint for every line of the stored procedure.
10) Repeat the above for the other two stored procedures. Put breakpoints everywhere!
11) Note that I have purposely put some useless lines of code in the stored procedures so that there are some variables whose values we could look up during the debug…just for learning purposes.
12) Run the project in Debug mode. Your browser will launch but before anything is displayed on the screen, you should hit a breakpoint in ShowFOO stored proc. F11 to go through it.
13) If haven’t already, open and observe the "Locals" window to see the value of variables in the stored procedures.
14) You could test my ASP.NET app by first Inserting some random text values...from the left pane of the screen...by viewing them in the mid section...and finally by deleting them in the right hand side of the screen.
Written by Montreal DBA Team
Download the scripts for this article (Prior to unzipping the content of the file you must Right-Click on the file --> Properties --> Unblock)