Export (0) Print
Expand All

Walkthrough: Debugging Hello World, a SQL Stored Procedure

This example shows how to start debugging a SQL stored procedure, set breakpoints, and view data items.

Note   To follow the steps in this example, you must have Visual Studio .NET Enterprise Developer or Visual Studio .NET Enterprise Architect Edition and SQL Server version 7.0 or later with SQL Debugging components installed (for more information, see Setting Up SQL Debugging).

To debug "Hello World"

  1. Start Visual Studio .NET.
  2. From the View menu, choose Server Explorer.
  3. In Server Explorer, right-click Data Connections and choose Add Connection.
  4. In the Data Link Properties dialog box, choose the Connection tab.
  5. On the Connection tab, in the Select or enter a server name box, select or enter the name of the server that your SQL Server is on (for SQL Server 2000, <machine_name\SQL_Group_Name>, for example).
  6. On the Connection tab, in the Select the database on the server box, select or enter the name of the database on your SQL server.
  7. On the Connection tab, enter the password to access the server or select Use Windows NT Integrated Security, depending on your type of security and credentials.
  8. In the Data Link Properties dialog box, click the Test Connection button to verify that the connection exists.
  9. Click OK.
  10. In Server Explorer, expand the Data Connections node. You should now see the database you are connected to.
  11. Expand the database node and right-click = the Stored Procedures node, and then select New Stored Procedure from the shortcut menu. The new stored procedure will automatically be opened in the Text Editor.
  12. Replace the contents of the default generated stored procedure with the following code:
    CREATE PROCEDURE HelloWorld
    AS
       DECLARE 
       @mynvarchar NVARCHAR(50),
       @myfloat FLOAT
    
       SET @mynvarchar = @@VERSION
       SET @mynvarchar  = 'Hello, world!'
       SET @myfloat = 1.6180
    
       PRINT @mynvarchar
       RETURN (0)
    
  13. From the File menu, click Save.

    The stored procedure named "HelloWorld" is saved and added under the Stored Procedures node associated with the database in Server Explorer.

  14. Right-click the HelloWorld stored procedure in the Text editor and, from the shortcut menu, choose Step Into Stored Procedure.
  15. When the instruction pointer, designated by a yellow arrow, appears, you are debugging your stored procedure. If you created the stored procedure as instructed above you will have stopped on the line SET @mynvarchar = @@VERSION, the first executable line of code in the stored procedure.
  16. Make the Locals window visible (on the Debug menu, click Windows, and then choose Locals). Notice that the parameters and local variables are displayed in the Locals window with their corresponding values. You can edit the values of the variables in the Locals window as the stored procedure runs. For more information, see Using the Locals Window.
    Note   The server may not reflect changes to values of variables in the debugger windows. For more information, see SQL Debugging Limitations.
  17. Press F10 to step one line in the stored procedure. Notice that the value of the variable @mynvarchar has changed in the Locals window and its value is now displayed in red indicating it has changed.
  18. Make the Watch window visible (on the Debug menu, click Windows, and then choose Watch). For more information, see Using the Watch Window.
  19. In the Text editor, double-click the @mynvarchar variable to select it. Drag @mynvarchar to the Watch window and drop it anywhere in the window. The variable is now added to the list of watched variables.
    Note   You can edit the values of variables in the Watch window as well.
  20. In the Text editor, right-click the Return (0) line and choose Insert Breakpoint from the shortcut menu.
  21. From the Debug menu, choose Continue.

    The debugger will stop on the breakpoint set in Step 13.

  22. Choose Continue again to finish debugging the stored procedure.

    You have successfully debugged your SQL stored procedure.

    Note   You can find more stored procedures by connecting to the pubs database.

See Also

Debugging SQL

Show:
© 2014 Microsoft