SQL Server 2008 passing datatype as parameter for

Question

Since SQL Server 2008 does not have a Try_convert function I wanted to implement it for use in my Project. (no simply updating to a more recent version is not an option)

Problem is: How can i pass a datatype as a parameter to my Function?

Or

More generic: What entry type does a variable need to be to be able to hold a datatype?

Basically I want to build a Function like this:

CREATE FUNCTION my_Try_Convert
(
    -- Add the parameters for the function here
    @value varchar(max),@entrytype type,
    @format int = NULL  
)
RETURNS typeof(@entrytype)
AS
BEGIN
    if @entrytype = typeof(datetime)
        if (select  ISDATE(@value))=1
            return convert(@entrytype,@value,@format)
    if @entrytype = typeof(int)     
        if (select  isnumeric(@value))=1
            return convert(@entrytype,@value,Null)
END
GO

What do I need to pass for @entrytypes type and for the typeOf psyodo-function?

Answer 1

Well, there is no TRY_CONVERT() or TRY_CAST() in SQL-Server 2008, but you can use XML's internal casts to nullable types.

Try this

DECLARE @tbl TABLE(SomeInt VARCHAR(100));
INSERT INTO @tbl VALUES('123')
                      ,('blah') --bad data
SELECT t.*
      ,CAST('' AS XML).value('sql:column("t.SomeInt") cast as xs:int?','int')
FROM @tbl t;

Bit of a hack... But somehow beautiful ;-)

Some explanantion

The CAST('' AS XML) is just a trick to get a real XML in order to get use of XML's native methods. The sql:column() is a XQuery-function, which allows you to include a set's column into the XQuery (use sql:variable for variables).

The cast as xs:int? will try to parse the string as int-value and will return NULL if this does not work.

The limit is: This will always use your system's defaults (similar to TRY_CAST). With TRY_CONVERT you'd have more control over the output with the third paramter...

Hint: XQuery/Xpath is strictly case-sensitive. So there is xs:dateTime?, but you'd get an error with xs:datetime...