CREATE FUNCTION dbo.GetGuestInstructorRoles(@AReferenceNumber INT, @AGuestNumber int)
RETURNS VARCHAR(1024)
AS
BEGIN
    DECLARE @r VARCHAR(1024)
	DECLARE @CategoryText VARCHAR(255)
	DECLARE tmpCursor CURSOR FOR
    SELECT Category.Text
    FROM ReservationCategoryProductXtab WITH (NOLOCK)
    INNER JOIN CategoryGroup WITH (NOLOCK) ON CategoryGroup.GroupNumber = ReservationCategoryProductXtab.FK_CategoryGroupNumber
    INNER JOIN Category WITH (NOLOCK) ON Category.CategoryNumber = ReservationCategoryProductXtab.FK_CategoryNumber
    WHERE ReservationCategoryProductXtab.ReferenceNumber = @AReferenceNumber
      AND ReservationCategoryProductXtab.GuestNumber = @AGuestNumber
	OPEN tmpCursor
	FETCH NEXT FROM tmpCursor INTO @CategoryText
	WHILE (@@FETCH_STATUS <> -1)
	BEGIN
	  IF ISNULL(@r, '') <> ''
	    SET @r = ISNULL(@r, '') + ','
	  SET @r = ISNULL(@r,'') + ISNULL(@CategoryText,'')
  	  FETCH NEXT FROM tmpCursor INTO @CategoryText
	END
	CLOSE tmpCursor
	DEALLOCATE tmpCursor
    RETURN @r
END
GO
