Friday, 22 September 2006

Custom aggregation in SQL Server - best solution ever (3)

I came across another elegant solution does the same job for custom aggregate. The only disadvantage of this approach is we need to write a function for each table needed. This is not a problem if performance is the key.

The function is quite short:

CREATE FUNCTIONS EmployeeList(@DeptID int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Result VARCHAR(8000) , @delim VARCHAR (2)

-- set empty string for delimitor. The comma would be set to it after the first select statement. Excelent!
SELECT @Result = ', ', @delim = ''
SELECT @Result = @Result + @delim + EmpID, @delim = ', '
FROM Emps
WHERE DeptID = @DeptID

RETURN @Result
END

This is the best approach I have seen so far and is very fast. It is about 3 time's faster than cursor approach. I have changed all my functions to follow this approach.

No comments: