Creating a CLR UDF with variable number of parameters

Question

Hi 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 ?

Answer 1

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.