Share via

Walkthrough: Debugging a Transact-SQL User-Defined Function

  • Article

This topic applies to:

Visual Studio Ultimate

Visual Studio Premium

Visual Studio Professional

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

This example uses an existing user defined function (UDF) named ufnGetStock in the AdventureWorks2008 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 Transact-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 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 example section below, and name it Test_ufnGetStock. For more information, see How to: Debug with a SQL Server Database Project or Server Project.

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

Other Resources

Server Explorer/Database Explorer