SQL How do you make a udf return null on error?

Question

I'm running on SQL server 2008. I am selecting a column type varchar and converting every value into an XML type. However, the varchar values might not always be in the correct XML format. If it is not in the correct XML format, I need to print out "ERROR" and then continue. The issue is that I'm not sure how to handle the conversion error. Tried using a TRY-CATCH around my select but that stops the select when an error is hit. I've resorted to creating a function to check that the conversion from varchar to XML will work or not and need the function to return null if the conversion causes an error. How would I do this?

This is very simplified version of the data i'm trying to convert

select dbo.isxml(val)

from (VALUES
('<\fewafeawf'), 
('<XML><STUFF DATA="TEST" /></XML>'),
('<XML>efaweff'))  X(val)

and this is the function I have right now. Can't figure out how to adjust it to return null if conversion fails

create function dbo.isxml(@NT varchar(max)) returns xml
as
begin

declare @output xml

set @output  = cast(@NT as xml)

return @output

end
go

The output I would want would be

NULL
<XML><STUFF DATA="TEST" /></XML>'
NULL

Tried using the TRY-CONVERT function but that is for sql server 2012. Tried using TRY-CATCH statement in the function but cant have TRY-CATCH statements in UDF functions. Thought of using procedures and try-catch within the procedures but I'm not really doing any updates or inserts so procedures doesn't really seem like what I should be using in this situation. Not sure how to handle this.

Any help would be great! Thank you!

score 0 · Answer 1 · answered Oct 02 '13 at 16:19

Been working on this for a while now and I cannot get it to work like you would ideally want it to work. You have mentioned (in your edit) some of the things I was going to mention like try_cast.

I did try several things and the only thing I could get to work will be slow. I created a SP with output paramater that you could call in a loop/cursor, and this works but it is not ideal.

ALTER PROCEDURE dbo.isxml2
    @NT varchar(max),
    @Result xml OUTPUT
AS
BEGIN
    SET NOCOUNT ON

    BEGIN TRY
    set @Result  = cast(@NT as xml)
    END TRY
    BEGIN CATCH
        set @Result = NULL
    END CATCH
END
GO

The only other option you have is to NOT call cast (in your UDF) unless it appears to be XML. This means you have to test it to see if it meets the requirements for CAST to work.

Or, Write a CLR to handle this, even worst.

SQL How do you make a udf return null on error?

1 Answers1