CODEWITHSUNDEEP
sql-serversqlclruser-defined-functions
josephj1989
josephj1989

Reputation: 9709

Creating a CLR UDF with variable number of parameters

I wanted a function to find the greatest of a list of String values passed in.

I want to invoke it as Select greatest('Abcd','Efgh','Zxy','EAD') from sql server. It should return Zxy. The number of parameters is variable.Incidentally it is very similar to oracle GREATEST function. So I wrote a very simple CLR function (Vs2008) and tried to deploy it. See below

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;

}
};

But when I try to compile or deploy it I get the following error Cannot find data type SqlString[].

Is it possible to satisfy my requirement using SQL CLR ?

Upvotes: 1

Views: 4284

Answers (3)

Solomon Rutzky
Solomon Rutzky

Reputation: 48826

No, it is not possible to have a variable number of parameters (i.e. the params modifier in .NET) in SQL Server User-Defined Functions, whether they are T-SQL or SQLCLR. Yes, some of the built-in functions do allow for such thing (e.g. CHECKSUM(*) ) but those are built directly into SQL Server and not to an API such as User-Defined Functions / Table-Valued Functions.

In order to best address the goal of this question, in what context are you getting these values? Are they multiple columns of a table or query? Are they various rows? Are these values already concatenated together as a CSV list? T-SQL actually does a pretty good job of sorting a list of things on its own. You might be able to structure your query to use OUTER APPLY (part of the FROM clause) which can be used to do this in various scenarios. For example:

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

The result is a "greatest" value among the 3 name fields on a per-row basis. And as you can see, this method is flexible enough to include any number of columns.

Upvotes: 2

Nenad Prekupec
Nenad Prekupec

Reputation: 1

Unfortunately there is no possibility of declaring UDF in CLR, with signature you would like (params SqlString[] p). UDF can only have strong type defined list of params, and keyword "params" is not supported currently (I hope this will change in the future also).

Here is the example of 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)));
    }

If you wish more params you will need to add another UDF.

[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)));
    }

Another thing to keep in mind in .CLR is there is no overloading of methods, so your UDF needs to have unique name.

At the end your UDF it's not possible to implement in .CLR if you have/want unlimited number of parameters. It can be only fixed number of parameters, eg. 4 (as in the case you mentioned).

Reason why to use CLR over SP in such cases is much better performance. But I would also point out that this doesn't mean you will get better performance with .CLR for every possible thing. In some cases T-SQL/PS will perform much better. Of course everything here depends on assumption that you can deploy .CLR in production environment at the end. If I can deploy .CLR to production and need math, string manipulation or similar things I always use CLR.

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300579

Here's a solution using a Table-Valued function:

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

Test:

DECLARE @test varchar(120)

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

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

GO

(Modified split function from here)

Note: This is almost certainly not the fastest way to do this. If you need to perform this millions of times, another solution may be more appropriate.

Upvotes: 2

Related Questions