Debugging UDFs

Or has it?

It seems that although the ability to debug a UDF was left out of Query Analyzer, UDF debugging capability exists in the database engine. The question remains, "How to get to this capability and debug that pesky UDF."

The answer turns out to be simple: Create a stored procedure that invokes the UDF and debug that. When the time comes to call the UDF, use the Step Into (usually F11) to enter the UDF.

To demonstrate the debugging procedure, let's start with the UDF that we want to debug. We'll use udf_TxtNSingleSpace which was published in Velum 1 #52. I include it here for your convenience. Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE FUNCTION dbo.udf_TxtN_SingleSpace (

    @sInput nvarchar(4000) -- String to modify to have only single spaces.
)   RETURNS nvarchar(4000) -- @sInput with single spaces between words.
    WITH SCHEMABINDING -- 
/* 
* Transforms a string by changing multiple spaces between words
* into single spaces.  Also trims leading and trailing spaces.
*
* Example:
select dbo.udf_TxtN_SingleSpace('   A  B C  D  EFGHI  J KlN    OP  ')
*
* Related code: udf_Txt_SingleSpace is an ASCII version.
*
* Test:
PRINT 'Test 1    ' + CASE WHEN 'A B C D EFGHI J KlN OP ' =
      dbo.udf_TxtN_SingleSpace ('  A  B C  D  EFGHI  J KlN    OP  ')
                     THEN 'Worked' ELSE 'ERROR' END
* History:
* When          Who     Description
* ------------- ------- -----------------------------------------
* 2003-11-01           ASN        Initial Coding
*
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* Published in T-SQL UDF of the Week Newsletter Vol 1 #52 
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN

DECLARE @Result nvarchar(4000)
DECLARE @Pos int -- Position in the @Input String
      , @Mark int -- Marker where searching from
      , @Len int -- Length of the input

SELECT @Pos = CHARINDEX (N'  ', @sInput, 1)
     , @Result = N'' -- zero length non-null string
     , @Mark = 1
     , @Len = LEN(@sInput)

WHILE @Pos > 0 BEGIN

    SELECT @Result = @Result 
                   + SUBSTRING (@sInput, @Mark, @Pos - @Mark + 1)
         , @Mark = @Pos + 1
                  + PATINDEX (N'%[^ ]%'
                             , substring(@sInput, @Pos+2 , @Len))

    SET @Pos = CHARINDEX(N'  ', @sInput, @Mark)

END -- WHILE

-- Now add from the end of the mark to the end of the string
SET @Result = LTRIM(RTRIM(@Result + SUBSTRING(@sInput, @Mark, @Len) ))

RETURN @Result
END
GO

GRANT EXEC on dbo.udf_TxtN_SingleSpace TO PUBLIC
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROC dbo.DEBUG_udf_TxtN_SingleSpace AS

PRINT 'Test 1    ' + CASE WHEN 'A B C D EFGHI J KlN OP ' =
      dbo.udf_TxtN_SingleSpace ('  A  B C  D  EFGHI  J KlN    OP  ')
                     THEN 'Worked' ELSE 'ERROR' END
GO

Once DEBUG_udf_TxtN_SingleSpace has been created, go to the Stored Procedures node of the Object Browser and refresh it. DEBUG_udf_TxtN_SingleSpace should now show up. Right click on the name to use the context menu for DEBUG_udf_TxtN_SingleSpace and select the last entry: DEBUG..., which will start the T-SQL debugger on the stored procedure.

Since the debugger is starting on the stored procedure, which doesn't have any parameters, there are no start-up parameters to supply, so you can press the execute key and begin the debugging session. Next use the Step Into tool (F11 on the keyboard) and step into the UDF. Now you can debug to figure out what's going wrong with your UDF.

Once you identify a change, you'll have to stop and close the debugger, ALTER your UDF and then restart the debugger. It isn't necessary to change DEBUG_udf_TxtN_SingleSpace unless you want to change the test that you're running.

If you're having trouble debugging T-SQL, it may have to do with the configuration of your system. There are a variety of prerequisites that must be met before T-SQL debugging works. You might want to take a look at the Microsoft knowledge base article, "INF: Transact-SQL Debugger Limitations and Troubleshooting Tips for SQL Server 2000", 280101 located at this URL: http://support.microsoft.com/default.aspx?scid=kb;en-us;280101

All-in-all, T-SQL debugging is adequate for the job and miles ahead of debugging with PRINT statements, which you can't have in a UDF anyway.

There is one more trick to UDF debugging. If you have Visual Studio.Net, it allows you to debug UDFs directly without creating a DEBUG stored procedure.