Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Function

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-08-12 : 10:06:22

Hi, I have this function:

ALTER FUNCTION [dbo].[ConcatCategories](@SchoolID VARCHAR(10))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SET @Output = ''

SELECT @Output = CASE @Output
WHEN '' THEN CAST(c.CategoryID AS VARCHAR)
ELSE @Output + ',' + CAST(c.CategoryID AS VARCHAR)
END
FROM
Activities a
INNER JOIN
SchoolActivities sa
ON
a.ActivityID = sa.ActivityID
INNER JOIN
Categories c
ON
a.ActivityCategory = c.CategoryID
where
sa.SchoolID=@SchoolID

RETURN @Output
END

How can I make this function return only unique values for the categoryid's

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-12 : 10:11:30

Why cant you use this straight away like the below -

SELECT @Output = @Output + ',' + DISTINCT CAST(c.CategoryID AS VARCHAR)
FROM Activities a
INNER JOIN SchoolActivities sa ON a.ActivityID = sa.ActivityID
INNER JOIN Categories c ON a.ActivityCategory = c.CategoryID
where sa.SchoolID=@SchoolID

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-08-12 : 10:16:27

Because the output might already contain items that will be added to the output.

The secret to creativity is knowing how to hide your sources. (Einstein)

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-12 : 10:49:06

Not getting what you want to say ?

There is no difference between your query and my query in terms of output

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-12 : 11:22:15

[code]
SELECT @Output = COALESCE(@Output + ',','') + Val
FROM
(SELECT DISTINCT CAST(c.CategoryID AS VARCHAR) AS Val
FROM Activities a
INNER JOIN SchoolActivities sa ON a.ActivityID = sa.ActivityID
INNER JOIN Categories c ON a.ActivityCategory = c.CategoryID
where sa.SchoolID=@SchoolID
)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources