Alter Function Repeats Same Value

Question

Alter Function Repeats Same Value

John Tate

SSC Rookie

Points: 44
More actions
September 19, 2007 at 5:12 pm

#120493

I have an alter function that repeats a value in the itemlist variable. I would like it to appear only once.
Example: plate, can, can, can, fork, knife, spoon
I would like this to read:
Example: plate, can, fork, knife, spoon
Any suggestions? My alter function is as follow:
set
ANSI_NULLS ON
set
QUOTED_IDENTIFIER ON
GO
ALTER
FUNCTION [dbo].[GetSpecialsInCase]
(
@TCaseId varchar
(25)
)
RETURNS VARCHAR
(2000)
AS
BEGIN
DECLARE
@itemList VARCHAR(8000)
SELECT
@itemList = IsNull(@itemlist + ', ' + char(13), space (0)) + SpecialsDesc
From
CaseToSpecialsMapping, SpecialsDesc
where
CaseToSpecialsMapping.SpecialsId = SpecialsDesc.SpecialsId
and
CaseToSpecialsMapping.TCaseId = @TCaseId
and
specials_outcome = 1
RETURN
@itemList
END

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Something like this will work...

ALTER FUNCTION [dbo].[GetSpecialsInCase]
(
@TCaseId varchar(25)
)
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @itemList VARCHAR(8000)
SELECT @itemList = IsNull(@itemlist + ', ' + char(13), space (0)) + d.SpecialsDesc
FROM
(--=== Derived table "d" finds correct distinct SpecialsDesc
SELECT DISTINCT SpecialsDesc
From CaseToSpecialsMapping, SpecialsDesc 
where CaseToSpecialsMapping.SpecialsId = SpecialsDesc.SpecialsId
and CaseToSpecialsMapping.TCaseId = @TCaseId
and specials_outcome = 1
) d

RETURN @itemList
END

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Tate SSC Rookie Points: 44 More actions · Answer 2

John Tate

SSC Rookie

Points: 44

September 20, 2007 at 9:42 am

#734986

Thank you. It worked great.