Walkthrough: Debug a Transact-SQL Stored Procedure

  • Article

This topic applies to:

Edition

Visual Basic

C#

C++

Web Developer

Express

 Topic does not apply Topic does not apply Topic does not apply Topic does not apply

Standard

 Topic does not apply Topic does not apply Topic does not apply Topic does not apply

Pro and Team

 Topic applies Topic applies Topic applies Topic applies

Table legend:

Topic applies

Applies
Topic does not apply

Does not apply
Topic applies but command hidden by default

Command or commands hidden by default.

This example shows how to create and debug a T-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 T-SQL Stored Procedure

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

  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: Develop with the SQL Server Project Type.

  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 AdventureWorks 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 SQL

Reference

Server Explorer/Database Explorer