UDF problem
-
I'm trying to use a UDF (we're on SQL Server 2005), and not getting it 100%.
The structure is Activities to Owners to SAN.
Activities can have 1 to many Owners, and the owners has a number (kina like SSN) linking to SAN which has their name.
USE DB
GO
CREATE FUNCTION dbo.ConcatOwners4(@ActID CHAR(50))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SET @Output = ''
SELECT @Output = CASE @Output
WHEN ''
THEN SAN.Authority
ELSE @Output + ', ' + SAN.Authority
END
FROM Activity Left Outer JOIN
Owner ON Activity.ActivityID = Owner.ActivityID Left outer JOIN
SAN ON Owner.OwnerZNumber = SAN.Authority
WHERE Activity.ActivityID = @ActID
ORDER BY Authority
RETURN @Output
END
GO
****************
...and run this:
SELECT DISTINCT ActivityID, ActivityDesc, dbo.ConcatOwners4(ActivityID) AS Expr1
FROM Activity
...I get nothing for the Owners names (Authority)
-
I don't think concatenation with an 'order by' is reliable, see this link
http://support.microsoft.com/default.aspx?scid=287515
As an alternative you can use 'for xml', something like this
CREATE FUNCTION dbo.ConcatOwners4(@ActID CHAR(50))RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SELECT @Output = SUBSTRING(
(SELECT ',' + SAN.Authority AS "text()"
FROM Activity
Left Outer JOIN Owner ON Activity.ActivityID = Owner.ActivityID
Left outer JOIN SAN ON Owner.OwnerZNumber = SAN.Authority
WHERE Activity.ActivityID = @ActID
ORDER BY Authority
FOR XML PATH('')) ,2,8000)
RETURN @Output
END
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark,
Thanks a ton...that did the trick!
UDF's sure are are great thing to have!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply