SQL How do you make a udf return null on error?
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
Any help would be great! Thank you!
No comments:
Post a Comment