用于替换 TRY_CONVERT 的 SQL CLR 函数 [英] SQL CLR Function to replace TRY_CONVERT

问题描述

我正在尝试编写自己的 CLR 函数来替换内置的TRY_CONVERT"sql 函数，因为我需要更多地控制日期和数字的转换方式(例如，内置函数无法处理包含科学数据的 DECIMAL 转换)符号).

I am trying to write my own CLR function to replace the built in 'TRY_CONVERT' sql function as I need more control over how dates and numbers are converted (e.g. the built-in function can't handle DECIMAL conversions that contain scientific notation).

我已经试过了:

[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static object TRY_CONVERT(SqlDbType type, SqlString input)
{
    switch (type)
    {
        case SqlDbType.Decimal:
            decimal decimalOutput;
            return decimal.TryParse(input.Value, NumberStyles.Any, CultureInfo.InvariantCulture, out decimalOutput) ? decimalOutput : (decimal?)null;
        case SqlDbType.BigInt:
            long bigIntOutput;
            return long.TryParse(input.Value, NumberStyles.Any, CultureInfo.InvariantCulture, out bigIntOutput) ? bigIntOutput : (long?)null;
        case SqlDbType.Date:
        case SqlDbType.DateTime:
        case SqlDbType.DateTime2:
            DateTime dateTimeOutput;
            return DateTime.TryParse(input.Value, CultureInfo.CreateSpecificCulture("en-GB"), DateTimeStyles.None, out dateTimeOutput) ? dateTimeOutput : (DateTime?)null;
        case SqlDbType.NVarChar:
        case SqlDbType.VarChar:
            return string.IsNullOrWhiteSpace(input.Value) ? null : input.Value;
        default:
            throw new NotImplementedException();
    }
}

但它不喜欢我构建时的 SqlDbType 类型.

but it doesn't like the SqlDbType type when I build.

是否可以传递内置函数中使用的target_type"，还是必须将其作为字符串传递或为我想使用的每种类型创建单独的 TRY_CONVERT 方法?

Is it possible to pass a 'target_type' as used in the built-in function or will I have to pass it as a string or create separate TRY_CONVERT methods for each type I want to use?

推荐答案

object 返回类型转换为 sql_variant 因此必须显式转换为正确的数据类型在 SQL 中，所以我认为解决这个问题的唯一方法是创建具有正确返回类型的单独 CLR 方法，如下所示:

The object return type translates to sql_variant so would have to be converted explicitly to the correct data type in SQL so I think the only way to solve this is to create separate CLR Methods that have the correct return types like this:

[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlDecimal TRY_CONVERT_DECIMAL(SqlString input)
{
    decimal decimalOutput;
    return !input.IsNull && decimal.TryParse(input.Value, NumberStyles.Any, CultureInfo.InvariantCulture, out decimalOutput) ? decimalOutput : SqlDecimal.Null;
}

[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlInt64 TRY_CONVERT_BIGINT(SqlString input)
{
    long bigIntOutput;
    return !input.IsNull && long.TryParse(input.Value, NumberStyles.Any, CultureInfo.InvariantCulture, out bigIntOutput) ? bigIntOutput : SqlInt64.Null;
}

[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlDateTime TRY_CONVERT_DATE(SqlString input)
{
    var minSqlDateTime = new DateTime(1753, 1, 1, 0, 0, 0, 0);
    var maxSqlDateTime = new DateTime(9999, 12, 31, 23, 59, 59, 0);
    DateTime dateTimeOutput;
    return !input.IsNull && DateTime.TryParse(input.Value, CultureInfo.CreateSpecificCulture("en-GB"), DateTimeStyles.None, out dateTimeOutput) &&
        dateTimeOutput >= minSqlDateTime && dateTimeOutput <= maxSqlDateTime ? dateTimeOutput : SqlDateTime.Null;
}

[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlString TRY_CONVERT_NVARCHAR(SqlString input)
{
    return input.IsNull || string.IsNullOrWhiteSpace(input.Value) ? SqlString.Null : input.Value;
}

这篇关于用于替换 TRY_CONVERT 的 SQL CLR 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！