Debugging Stored Procedures

Business logic in a software application inevitably is spread out over servers, web servers, web services, assemblies, etc. Some logic may be in a C# assembly while other portions of the logic lie in SQL Server. In Visual Studio, debugging the C# code is a piece of cake (I didn't say fun J, just simple to step through code).

Transact SQL has always been another animal. "If I had a nickel for every print statement…" It generally involves opening SQL Management Studio, testing smaller statements, creating a larger stored procedure and executing the stored procedure on its own.

Visual Studio has a couple tools to make the entire SQL debug thing about as easy as C# code. It's true, no smiley face! We'll get into two main topics. The first is debugging a stored procedure on its own. Second (ready for this?), actually stepping through C# code and directly into the stored procedure when called by the C# code. Let's go!

1. The Stored Procedure
   1. Start the SQL Management Studio
   2. Must log with Windows Authentication with sysadmin privileges
   3. Create a database called Testing
   4. In the Testing database, create a stored procedure to simply return the current date

      CREATE PROCEDURE [dbo].[GET_CURRENT_DATE]
      AS
      BEGIN
          -- SET NOCOUNT ON added to prevent extra result sets from
          -- interfering with SELECT statements.
          SET NOCOUNT ON
      
          -- Insert statements for procedure here
          SELECT GETDATE()
      END
      GO

2. Debugging the Stored Procedure
   1. Start Visual Studio
   2. Connect to the Testing Database
   3. Open the Server Explorer (Ctrl + Alt + S)
      1. Right click Data Connections
      2. Click Add Connection
      3. In the Add Connection Dialog, connect to the Testing database
      4. Use Windows Authentication
      5. Again, need the sysadmin privileges
      6. Click OK
   4. Back in the Server Explorer
      1. Expand the server
      2. Right click the server and ensure Application Debugging is checked
      3. Expand Stored Procedures
      4. Double click the GetTheCurrentDate procedure
      5. This opens the stored procedure in the editor window
      6. Right click some white space and click Step Into Stored Procedure
      7. F11, Local Variables, Watch, Breakpoints, all of it works the same as C#
      8. How cool is that!
3. Stepping from C# into the Stored Procedure
   1. Create a console application
   2. In the Solution Explorer, double click the Properties folder
   3. Click the Debug tab
   4. Ensure Enable SQL Server Debugging is checked
   5. In the Main method
      1. Create a SQL Connection to the testing database
      2. Create a SQL Command on that connection for the GetTheCurrentDate procedure
      3. Set the command type to stored procedure
      4. Open the connection
      5. Call the SqlCommand.ExecuteScalar() method
      6. Don't run it yet
   6. Open the stored procedure as in step # 2
   7. Insert a breakpoint on the line with the select statement
   8. Switch back the C# code
   9. F11 to step down to the execute scalar line
   10. F11 one more time
   11. Boom, you should be at the breakpoint in the stored procedure!

Who wants cake? Pretty wild. It should be obvious at this point all of the advantages of having the ability to debug the Transact SQL.

A lot of connections use SQL Authentication, this is fine for debugging. Only the connection created in the Server Explorer needs the Windows/Sysadmin rights. It's on this connection that the debugging is actually performed. There aren't any limitations on the connection string used in the C# code in the application.