Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All
Important This document may not represent best practices for current development, links to downloads and other resources may no longer be valid. Current recommended version can be found here.

Walkthrough: Debug a T-SQL Stored Procedure

This topic applies to:

Visual Studio Edition

Visual Basic

C#

C++

J#

Visual Web Developer

Express

No

No

No

No

No

Standard

No

No

No

No

No

Pro/Team

Yes

Yes

Yes

Yes

Yes

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.

NoteNote

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

Community Additions

ADD
Show:
© 2015 Microsoft