代码之家 › 专栏 › 技术社区 › josephj1989

创建参数数目可变的CLR UDF

user-defined-functions sqlclr sql-server

1

josephj1989 · 技术社区 · 15 年前

我想要一个函数来查找传入的字符串值列表中的最大值。

我想从sql server中调用它作为Select greatest('Abcd','Efgh','Zxy','EAD')。它应该返回Zxy。甲骨文最大的功能。所以我编写了一个非常简单的CLR函数(Vs2008),并尝试部署它。见下文

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString Greatest(params SqlString[] p)
{
SqlString max=p[0];
foreach (string s in p)
max = s.CompareTo(max) > 0 ? s : max;

return max;

}
};

但是当我试图编译或部署它时,我会得到以下错误

是否可以使用SQL CLR满足我的需求?

3 回复 | 直到 13 年前

1

2

Mitch Wheat 15 年前

下面是一个使用表值函数的解决方案:

CREATE FUNCTION fn_Split
(
    @text VARCHAR(8000), 
    @delimiter VARCHAR(20) = ','
)
    RETURNS @Strings TABLE 
        (
            position INT IDENTITY PRIMARY KEY,
            value VARCHAR(8000)
        )
AS BEGIN
    DECLARE @index int
    SET @index = -1

    WHILE (LEN(@text) > 0) BEGIN
        -- Find the first delimiter
        SET @index = CHARINDEX(@delimiter , @text)

        -- No delimiter left?
        -- Insert the remaining @text and break the loop
        IF (@index = 0) AND (LEN(@text) > 0) BEGIN  
            INSERT INTO @Strings VALUES (LTRIM(RTRIM(@text)))
            BREAK 
        END 

        -- Found a delimiter
        -- Insert left of the delimiter and truncate the @text
        IF (@index > 1) BEGIN
            INSERT INTO @Strings VALUES (LTRIM(RTRIM(LEFT(@text, @index - 1))))
            SET @text = RIGHT(@text, (LEN(@text) - @index))
        END
        -- Delimiter is 1st position = no @text to insert
        ELSE SET @text = RIGHT(@text, (LEN(@text) - @index))
    END
    RETURN
END
GO

测试:

DECLARE @test varchar(120)

SET @test = 'Abcd, Efgh, Zxy, EAD'

SELECT Top(1) value FROM dbo.fn_Split(@test, ',')
ORDER BY value DESC

GO

here )

注意

2

1

Solomon Rutzky 10 年前

不,不可能有可变数量的参数(即 params .NET中的修饰符)在SQL Server用户定义函数中,无论它们是T-SQL还是SQLCLR。是的,一些内置函数确实允许这样做(例如。 CHECKSUM(*) )但是,它们直接构建在SQL Server中,而不是构建在API中,比如用户定义的函数/表值函数。

OUTER APPLY (部分 FROM 子句),可用于在各种情况下执行此操作。例如:

SELECT tab.name AS [TableName],
       ind.name AS [IndexName],
       col.name AS [ColumnName],
       greatest.Item AS [GREATEST()]
FROM   sys.tables tab
LEFT JOIN (sys.indexes ind
    INNER JOIN sys.index_columns indcol
            ON indcol.[object_id] = ind.[object_id]
           AND indcol.index_id = ind.index_id
    INNER JOIN sys.columns col
            ON col.[object_id] = indcol.[object_id]
           AND col.column_id = indcol.column_id
          )
       ON ind.[object_id] = tab.[object_id]
OUTER APPLY (SELECT TOP 1 tmp.Name AS [Item]
             FROM (
                    SELECT tab.name UNION ALL SELECT ind.name UNION ALL SELECT col.name
                  ) tmp(Name)
             ORDER BY tmp.Name ASC
            ) greatest

结果是每行3个名称字段中的“最大”值。如您所见,这个方法足够灵活,可以包含任意数量的列。

3

0

Nenad Prekupec 10 年前

不幸的是,不可能在CLR中使用您想要的签名(params SqlString[]p)声明UDF。自定义项只能有强类型定义的参数列表,当前不支持关键字“params”(我希望将来也会有所改变)。

下面是String.Format UDF的示例。

[SqlFunction(DataAccess = DataAccessKind.None)]
    public static SqlString clr_StringFormat2(SqlString format, object s1, object s2)
    {
        return format.IsNull ? SqlString.Null : new SqlString(string.Format(format.Value, SqlTypeToNetType(s1, s2)));
    }

[SqlFunction(DataAccess = DataAccessKind.None)]
    public static SqlString clr_StringFormat3(SqlString format, object s1, object s2, object s3)
    {
        return format.IsNull ? SqlString.Null : new SqlString(string.Format(format.Value, SqlTypeToNetType(s1, s2, s3)));
    }

另一件要记住的事情是.CLR没有方法重载,所以您的UDF需要有唯一的名称。

在UDF的末尾,如果您有/想要无限数量的参数,则无法在.CLR中实现。它只能是固定数量的参数,例如4(如您提到的情况)。

在这种情况下使用CLR over SP的原因是性能更好。但我也要指出的是,这并不意味着你会在使用.CLR时获得更好的性能。在某些情况下,T-SQL/PS的性能会更好。