Alter Function Repeats Same Value

Question

Post reply

Alter Function Repeats Same Value

John Tate

SSC Rookie

Points: 34
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: 1000539 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)
Intro to Tally Tables and Functions

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

John Tate

SSC Rookie

Points: 34

September 20, 2007 at 9:42 am

#734986

Thank you. It worked great.