Compartilhar via

Walkthrough: Debug a Transact-SQL Stored Procedure

  • Artigo

This topic applies to:

Visual Studio Ultimate

Visual Studio Premium

Visual Studio Professional

Visual Studio Express
yet1b7by.DoesApplybmp(en-us,VS.100).gif yet1b7by.DoesApplybmp(en-us,VS.100).gif yet1b7by.DoesApplybmp(en-us,VS.100).gif yet1b7by.DoesNotApplybmp(en-us,VS.100).gif

This example shows how to create and debug a Transact-SQL stored procedure by Direct Database Debugging, in other words, stepping into the stored procedure using Server Explorer. It also illustrates different debugging techniques such as setting breakpoints, viewing data items, and so on.

Note

The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Visual Studio Settings.

To debug a Transact-SQL stored procedure

  1. In a new SQL Server project, establish a connection to the AdventureWorks2008 sample database. For more information, see How to: Connect to a Database with Server Explorer.

  2. Create a new stored procedure using the code from the first example section below, and name it HelloWorld. For more information, see How to: Debug with a SQL Server Database Project or Server Project.

  3. Set breakpoints in HelloWorld, and step into the stored procedure. For more information, see How to: Step into an Object Using Server Explorer. The instruction pointer, designated by a yellow arrow, will appear on the line SET @mynvarchar = @@VERSION, the first executable line of code in the stored procedure.

  4. Try out different debugging features.

    1. Make the Locals window visible. To do so, on the Debug menu, click Windows, and then click 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 How to: Use Debugger Variable Windows.

      Note   The server may not reflect changes to values of variables in the debugger windows. For more information, see SQL Debugging Limitations.

    2. 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.

    3. Make the Watch window visible. To do so, on the Debug menu, click Windows, and then choose Watch. For more information, see How to: Use Debugger Variable Windows.

    4. In the Text Editor, double-click the @mynvarchar variable to select it. Drag @mynvarchar to any location on the Watch 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.

    5. In the Text Editor, right-click the line Return (0), and on the shortcut menu, click Insert Breakpoint.

    6. On the Debug menu, click Continue.

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

    Note   You can step into any of the stored procedures in the AdventureWorks2008 database that are displayed under the Stored Procedures node associated with it.

Example

This is the code for the stored procedure.

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)

See Also

Concepts

Debugging Transact-SQL

Other Resources

Server Explorer/Database Explorer