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.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment