Nested cursors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Nested cursors

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-04-04 : 16:46:00

I have a table like:

Converted_Mortgage_Number Activity_Date Activity_Time Sequence_Number, History_Text

100 2006-01-20 84936 1 Sent a reminder letter
105 2006-01-23 134502 1 Called, but no reply
100 2006 01-24 104532 1 Asked to call again later
100 2006-01-24 104532 2 when it is more convenient
100 2006-01-24 104532 3 and the person I require is available

I wish to concantenate the History_text fields together for records with the same Converted_Mortgage_Number, Activity_Date and Activity_Time so:

100 2006-10-24 104532 Asked to call again later when it is moreconvenient and the person I require is available.

To do this I amusing nested cursors so:


USE ar
GO
DECLARE @mortgage INT,
		@date datetime,
		@time INT,
		@getMortgage CURSOR,
		@notes varchar(MAX),
		@notesComplete varchar(MAX),
		@getDetail CURSOR

SET @getMortgage = 	CURSOR FOR
					SELECT  Converted_Mortgage_Number, Activity_Date, Activity_Time
					FROM format_history
					GROUP BY Converted_Mortgage_Number, Activity_Date, Activity_Time
					ORDER BY Converted_Mortgage_Number ASC
OPEN @getMortgage
	FETCH NEXT
	FROM @getMortgage INTO @mortgage, @date, @time
WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @notes = ''
		SET @getDetail = CURSOR FOR
			SELECT History_Text
			FROM  format_history
			WHERE Converted_Mortgage_Number = @mortgage AND Activity_Date = @date AND Activity_Time = @time
			ORDER BY Sequence_Number
		OPEN @getDetail
		SET @notesComplete = ''
		FETCH NEXT FROM @getDetail INTO @notes
		WHILE (@@FETCH_STATUS = 0)
		  BEGIN
		   @notesComplete = @notesComplete + @notes + ' '
		   FETCH NEXT FROM @getDetail INTO @notes
		  END
		PRINT @notesComplete
		CLOSE @getDetail
		DEALLOCATE @getDetail
		FETCH NEXT
		FROM @getMortgage INTO @mortgage, @date, @time
	END
CLOSE @getMortgage
DEALLOCATE @getMortgage
GO

But I am getting the error: 'Incorrect syntax near the keyword CLOSE, incorrect syntax near @notesComplete'.

Can anyone please see where I am going wrong?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-04 : 16:49:15

Don't put the @ before the name of the cursor. Check BOL for cursor examples.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

ranganath
Posting Yak Master

209 Posts

Posted - 2008-04-05 : 01:12:11

Hi,

Try with this

Declare @T Table (Converted_Mortgage_Number Int, Activity_Date varchar(1000), Activity_Time Int, Sequence_Number Int, History_Text varchar(1000))
Insert into @T
Select 100, '2006-01-20' ,84936 ,1, 'Sent a reminder letter' Union all
Select 105, '2006-01-23' ,134502 ,1, 'Called, but no reply' Union all
Select 100, '2006-01-24' ,104532 ,1, 'Asked to call again later' Union all
Select 100, '2006-01-24' ,104532 ,2, 'when it is more convenient' Union all
Select 100, '2006-01-24' ,104532 ,3, 'and the person I require is available'

--Select * From @T

select Converted_Mortgage_Number, Activity_Date, Activity_Time, STUFF( ( select ', '+ History_Text from @T A where A.Converted_Mortgage_Number = B.Converted_Mortgage_Number
and A.Activity_Date = B.Activity_Date and A.Activity_Time = B.Activity_Time for xml path('')),1,1,'')as 'History_Text'
from @T B
Group By Converted_Mortgage_Number, Activity_Date, Activity_Time

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-04-05 : 07:44:23

quote:
Originally posted by tkizer

Don't put the @ before the name of the cursor. Check BOL for cursor examples.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Strange, as the 'outer' cursor works OK. It's just the inner loop I'm having trouble with.

Can anyone who understands cursors see why this should be please?

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-04-05 : 08:09:07

Holdon, I've just realised that I only need to replace

@notesComplete = @notesComplete + @notes + ' '

with

SET @notesComplete = @notesComplete + @notes + ' '

and it works fine.

Don't know why I didn't see that last night when I first posted - tired I guess ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources