Fetch Next From Cursor 2 values into 2 variables

I am trying to create a UDF.  My code is attached.

For each row retrieved by the SELECT statement there will be 2 values.  

What is the corret syntax to access both retrieved values?  What should I use instead of:
            FETCH NEXT FROM BusinessCommentCursor
            INTO @Business_Comment, @Employee_Name

ALTER FUNCTION [dbo].[Assemble_Business_Comments]
	(@Apps_Cat_ID int)
	RETURNS varchar(8000)
	AS
	BEGIN
		DECLARE @Employee_Name varchar(100)
		DECLARE @Business_Comment varchar(8000)
		DECLARE @Business_Comments varchar(8000)
		DECLARE BusinessCommentCursor CURSOR READ_ONLY FOR
		SELECT Tbl_Survey_Business_Respondents.Respondent_Comment, Tbl_Client_Employees.Employee_Name
			FROM Tbl_Survey_Business_Respondents INNER JOIN
                      Tbl_Client_Employees ON 
                      Tbl_Survey_Business_Respondents.Lookup_To_Tbl_Client_Employees_For_Business_Respondent = Tbl_Client_Employees.ID
			WHERE (Tbl_Survey_Business_Respondents.Apps_Cat_ID = @Apps_Cat_ID)
		OPEN BusinessCommentCursor
 
		FETCH NEXT FROM BusinessCommentCursor
		INTO @Business_Comment, @Employee_Name
 
		WHILE @@FETCH_STATUS = 0
		BEGIN
			IF @Business_Comment is not null 
				SET @Business_Comments = @Business_Comments + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + @Employee_Name
				SET @Business_Comments = @Business_Comments + CHAR(13) + CHAR(10) + @Business_Comment
			FETCH NEXT FROM BusinessCommentCursor
			INTO @Business_Comment, @Employee_Name
		END
		CLOSE BusinessCommentCursor
		DEALLOCATE BusinessCommentCursor
		IF @Business_Comment is null
			SET @Business_Comments = N'None provided'
      RETURN (@Business_Comments)
	END

Open in new window

LVL 1
wsturdevAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is this sql 2005?
then, change the NTEXT to NVARCHAR(MAX), and your VARCHAR(8000) in the function to NVARCHAR(MAX).
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the syntax is correct... however, you cannot use cursors in functions if I rmember correctly...
now, you have quite some logic errors in the code...

let me suggest this code:
ALTER FUNCTION [dbo].[Assemble_Business_Comments]
      (@Apps_Cat_ID int)
      RETURNS varchar(8000)
      AS
      BEGIN
        DECLARE @res VARCHAR(8000) 
        SELECT @res = COALESCE(@res + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) , '') 
                    + c.Employee_Name 
                    + COALESCE( CHAR(13) + CHAR(10) + r.Respondent_Comment, '' )
          FROM Tbl_Survey_Business_Respondents r
          INNER JOIN Tbl_Client_Employees c
             ON r.Lookup_To_Tbl_Client_Employees_For_Business_Respondent = c.ID
          WHERE r.Apps_Cat_ID = @Apps_Cat_ID 

         IF @res is null
           SET @res = N'None provided'
          RETURN (@res)
        END

Open in new window

0
 
wsturdevAuthor Commented:
I guess my logic was kind of primitive!!!!

I am now getting this error:
Msg 402, Level 16, State 1, Procedure Assemble_Business_Comments, Line 9
The data types char and ntext are incompatible in the add operator.
0
 
wsturdevAuthor Commented:
Clicked Submit too soon...  Respondent_Comment is defined as ntext.
0
 
wsturdevAuthor Commented:
Thanks!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.