Solved

Fetch Next From Cursor 2 values into 2 variables

Posted on 2008-06-23
5
6,044 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:wsturdev
  • 3
  • 2
5 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21849702
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
 
LVL 1

Author Comment

by:wsturdev
ID: 21849756
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
 
LVL 1

Author Comment

by:wsturdev
ID: 21849774
Clicked Submit too soon...  Respondent_Comment is defined as ntext.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21849801
is this sql 2005?
then, change the NTEXT to NVARCHAR(MAX), and your VARCHAR(8000) in the function to NVARCHAR(MAX).
0
 
LVL 1

Author Closing Comment

by:wsturdev
ID: 31469913
Thanks!!
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
join 2 views with 5 conditions 3 54
SQL Server syntax question 13 30
Sql server function help 15 28
Get number of Files in Directory and Sub Directories 2 39
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now