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
