Fetch Returning Duplicate Last Record

Ok, this thing is returning the last record twice. If I have only one record it returns it twice, multiple records gives me the last one twice. I am sure some dumb pilot error is involved, HELP!

Thanks in advance, Larry

ALTER FUNCTION dbo.TestFoodDisLikes

(

@.ResidentID int

)

RETURNS varchar(250)

AS

BEGIN

DECLARE @.RDLike varchar(50)

DECLARE @.RDLikeList varchar(250)

BEGIN

SELECT @.RDLikeList = ''

DECLARE RDLike_cursor CURSOR

LOCAL SCROLL STATIC

FOR

SELECT FoodItem

FROM tblFoodDislikes

WHERE (ResidentID = @.ResidentID) AND (Breakfast = 'True')

OPEN RDLike_cursor

FETCH NEXT FROM RDLike_cursor

INTO @.RDLike

SELECT @.RDLikeList = @.RDLike

WHILE @.@.FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM RDLike_cursor

INTO @.RDLike

SELECT @.RDLikeList = @.RDLikeList + ', ' + @.RDLike

END

CLOSE RDLike_cursor

DEALLOCATE RDLike_cursor

END

RETURN @.RDLikeList

END

Your selecting into the cursor before any operation in the begin statement. ****

ALTER FUNCTION dbo.TestFoodDisLikes
(
@.ResidentID int
)
RETURNS varchar(250)
AS
BEGIN
DECLARE @.RDLike varchar(50)
DECLARE @.RDLikeList varchar(250)
BEGIN
SELECT @.RDLikeList = ''
DECLARE RDLike_cursor CURSOR
LOCAL SCROLL STATIC
FOR
SELECT FoodItem
FROM tblFoodDislikes
WHERE (ResidentID = @.ResidentID) AND (Breakfast = 'True')
OPEN RDLike_cursor
FETCH NEXT FROM RDLike_cursor
INTO @.RDLike
SELECT @.RDLikeList = @.RDLike
WHILE @.@.FETCH_STATUS = 0
BEGIN --****

SELECT @.RDLikeList = @.RDLikeList + ', ' + @.RDLike--****
FETCH NEXT FROM RDLike_cursor INTO @.RDLike--****

END--****
CLOSE RDLike_cursor
DEALLOCATE RDLike_cursor
END
RETURN @.RDLikeList
END

|||

Steve,

It still duplicates one record, that just moved it to the beginning. If I am expecting to see somethimg like 'Ham, Beans, Biscuit, Apples, Beets' I get 'Ham, Ham, Beans, Biscuit, Apples, Beets'. Looks like the second part of the FETCH is starting all over again

|||Actually I would avoid using a cursor for this, according to the Northwind database it would be something like this:

DECLARE @.Cities VARCHAR(8000)

SET @.Cities = ''

SELECT @.Cities = CASE @.Cities

WHEN '' THEN City

ELSE @.Cities + ', ' + City

END

from CUstomers

Group by City

Select @.Cities

According to your problem it would be

DECLARE @.ResidentID VARCHAR(8000)

DECLARE @.FoodItems VARCHAR(8000)

SET @.FoodItems = ''

SELECT @.FoodItems =CASE @.FoodItems

WHEN '' THEN FoodItem

ELSE @.FoodItems + ', ' + FoodItem

END

from tblFoodDislikes

WHERE (ResidentID = @.ResidentID) AND (Breakfast = 'True')

Group by FoodItem

Select @.FoodItems

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens, That works great and I did use it, seems faster. I am still at a loss as to why the fetch was returning a duplicate record but I like this better.

Larry