Walkthrough: Debugging a Transact-SQL User-Defined Function

  • 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 uses an existing User Defined Function (UDF) named ufnGetStock in the AdventureWorks database. This function returns a count of items in stock for a given ProductID.

The example creates a stored procedure that calls the UDF several times. The stored procedure calls the UDF with different parameters, so you can follow different execution paths through the UDF. The example also illustrates jumping from one T-SQL object, the stored procedure, to another, the function. The steps are similar to those in Walkthrough: Debug a Transact-SQL Stored Procedure.

You can also do Direct Database Debugging by stepping into the function in Server Explorer.

To debug a User Defined Function

  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 example section below, and name it Test_ufnGetStock. For more information, see How to: Develop with the SQL Server Project Type.

  3. Set breakpoints in Test_ufnGetStock. This is optional, because Direct Database Debugging causes the first line of the procedure to act as a breakpoint.

  4. Set breakpoints in the UDF ufnGetStock.

    1. Open the source code for the UDF by right-clicking the Functions node in Server Explorer, then double-clicking the ufnGetStock icon.

    2. Left-click in the gray margin next to the DECLARE statement to set a breakpoint.

  5. Step into the stored procedure. If the procedure had parameters, the Run Stored Procedure dialog box would have appeared, asking for their values. In this case, the parameters are all hard-coded inside the stored procedure. For more information, see How to: Step into an Object Using Server Explorer.

  6. Try out different debugging features.

    1. Step through the code using the F11 key or the Step Into button. At the SELECT statement, when you press F11 again, you will step into the UDF. Step through the UDF until you exit back to the stored procedure, and continue.

    2. You can observe different execution paths in the UDF depending on the input parameters. You can see the values of variables in UDFs in the Locals window, or by placing your cursor over them.

Example

This is the code for the stored procedure that calls the user-defined function.

ALTER PROCEDURE Test_ufnGetStock 
AS
    SELECT dbo.ufnGetStock(1) as CurrentStock   -- non-zero inventory
    SELECT dbo.ufnGetStock(316) as CurrentStock -- zero inventory
    SELECT dbo.ufnGetStock(5) as CurrentStock   -- no such product

See Also

Concepts

Debugging SQL

Reference

Server Explorer/Database Explorer