Learn how to use Visual Studio to debug Transact SQL stored procedures or functions.
SQL statements can be difficult to diagnose and debug. SQL Server does not include any default way to debug and step through a stored procedure, but Visual Studio does. Using the Server Explorer, you can step through the execution of a stored procedure or function right inside of Visual Studio. The first step is to open the Server Explorer and create a data connection to your database [Hack #74] .
You will then see the stored procedures and functions of your database listed in the Server Explorer; these objects for the Northwind database are shown in Figure 5-21.
From the Server Explorer, you can right-click on a stored procedure or function and you will see a menu item named Step Into Stored Procedure, as shown in Figure 5-22.
When you select Step Into Stored Procedure, you will see the Run Stored Procedure dialog that is shown in Figure 5-23.
After specifying the values for any parameters the stored procedure has, click the OK button. Visual Studio will now execute the stored procedure and open it in the document window, stopping in the first line of execution. This is shown in Figure 5-24.
You can now step through the stored procedure as it executes. You can set breakpoints just as you would in normal codethe only limitation is that you can specify only location and hit count breakpoints. The applicable debug windows also work. Figure Figure 5-25 shows an example of the Locals window displaying the values of the various parameters.
Because T-SQL is inherently different than .NET languages, the debugging experience is a little bit different. Here are some of the limitations with SQL debugging:
You can use only location and hit count breakpoints in T-SQL stored procedures and functions.
You cannot use Step Into to step from .NET managed code to T-SQL. You can set breakpoints in the stored procedure though, and the debugger will break when it comes across them.
You cannot use Break while a SQL statement is already running.
You can't use the Set Next Statement function as you might in managed code.
Some other differences are the facts that you can't use the memory or registers windows, as they just don't apply to SQL. Unfortunately, SQL Print statements are not shown in the output window either.
You cannot run triggers directly, but you can set breakpoints in triggers, and if they are triggered, Visual Studio will break into their execution on those breakpoints.
You can configure Visual Studio and SQL Server to debug stored procedures and functions that are located on a remote server. Enabling this has security implications, and I would not recommend it on a production SQL Server. The process for enabling this feature includes quite a few steps, which are detailed at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsdebugl/vxlrfSettingUpSQLDebugging.asp.
Being able to debug T-SQL stored procedures, functions, and triggers can be extremely helpful when trying to find errors or follow the execution of your application.