Walkthrough: Debugging a Transact-SQL User-Defined Function
This topic applies to:
Pro and Team
Does not apply
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
In a new SQL Server project, establish a connection to the AdventureWorks sample database. For more information, see How to: Connect to a Database.
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.
Set breakpoints in Test_ufnGetStock. This is optional, because Direct Database Debugging causes the first line of the procedure to act as a breakpoint.
Set breakpoints in the UDF ufnGetStock.
Open the source code for the UDF by right-clicking the Functions node in Server Explorer, then double-clicking the ufnGetStock icon.
Left-click in the gray margin next to the DECLARE statement to set a breakpoint.
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.
Try out different debugging features.
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.
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.
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