(c) 2002 Visual Studio Magazine 
Fawcette Technical Publications

Issue: November 2002
Section: Database Design
Author: Dianne Siebold

T-SQL	Debug User-Defined Functions
Listing A	You can debug user-defined functions (UDFs) like any T-SQL code by stepping into the stored procedure that calls them.  The stored procedure usp_GetEmployee calls the function ufn_GetEmployeeRegionString to return an employee's regions as part of a SELECT statement.

CREATE PROCEDURE usp_GetEmployee
@EmployeeID int
AS

SET NOCOUNT ON

SELECT	e.FirstName,
		e.LastName,
		dbo.ufn_GetEmployeeRegionString
			(@EmployeeID)
FROM Employees e
WHERE e.EmployeeID = @EmployeeID
GO


CREATE FUNCTION dbo.ufn_GetEmployeeRegionString
(@EmployeeID int)
RETURNS varchar(1000) AS  
BEGIN 

DECLARE	@RegionDescription varchar(50),
		@RegionString varchar(1000)

DECLARE CurEmployeeRegions CURSOR FOR

SELECT DISTINCT r.RegionDescription
FROM Region r
INNER JOIN Territories t ON r.RegionID = 
	t.RegionID
INNER JOIN EmployeeTerritories et ON t.TerritoryID 
	= et.TerritoryID
INNER JOIN Employees e ON et.EmployeeID = 
	e.EmployeeID
WHERE e.EmployeeID = @EmployeeID
ORDER BY RegionDescription

IF @EmployeeID IS NOT NULL
	BEGIN
		OPEN CurEmployeeRegions
		FETCH NEXT FROM CurEmployeeRegions
		INTO @RegionDescription

		WHILE @@FETCH_STATUS = 0
			BEGIN
				SELECT @RegionString = isNull 
					(@RegionString, '') + 
					RTRIM(@RegionDescription) + ', '
				FETCH NEXT FROM CurEmployeeRegions
				INTO @RegionDescription 
			END
			CLOSE CurEmployeeRegions
			DEALLOCATE CurEmployeeRegions

		SELECT @RegionString = 
			substring(@RegionString, 1, 
			datalength(@RegionString) -2)
	END

	RETURN @RegionString
END

VB.NET	Step Through the Stored Procedure
Listing 1	The debugger stops at the first breakpoint set in the stored procedure once the code calls the command object's ExecuteReader method. You must set a breakpoint after the ExecuteReader method becase the code continues to execute when the debugger returns to VS.NET.

Private Sub btnDisplayRegions_Click(ByVal sender _
	As System.Object, ByVal e As _
	System.EventArgs) Handles _
	btnDisplayRegions.Click

	Dim ConnectionString As String
	Dim cmd As New SqlClient.SqlCommand()
	Dim dr As SqlClient.SqlDataReader

	'Check for data in text box and make sure it's 
	'numeric
	If txtEmployeeID.TextLength = 0 Then
		MessageBox.Show _
			("Please enter an Employee ID")
		Exit Sub
	End If

	If Not IsNumeric(txtEmployeeID.Text) Then
		MessageBox.Show _
			("Please enter an Employee ID")
		Exit Sub
	End If

	Try

		'Set the connection string to local 
		'database
		ConnectionString = _
			"Data Source=(local);Initial _
			Catalog=Northwind;Integrated _
			Security=SSPI"

		'Call the stored procedure
		With cmd
			.CommandType = _
				CommandType.StoredProcedure
			.CommandText = "usp_GetEmployee"
			.Parameters.Add("@EmployeeID", _
				SqlDbType.Int).Value = _
				Trim(txtEmployeeID.Text)

			.Connection = New _
				SqlConnection(ConnectionString)
			.Connection.Open()
			dr = .ExecuteReader
			Do While dr.Read
				txtFirstName.Text = dr.GetString(0)
				txtLastName.Text = dr.GetString(1)
				txtRegions.Text = dr.GetString(2)
			Loop
		End With

	Catch Ex As Exception
		MessageBox.Show(Ex.Message)

	End Try
	End Sub

