Cursor Statement Help - 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 2000 Forums

Transact-SQL (2000)

Cursor Statement Help

Author

Topic

galbrecht
Starting Member

17 Posts

Posted - 2008-02-24 : 19:39:43

Hi

I have a cursor wich works, but it seems to t=be truncating some of the data. There are two columns that should have 6 sets of adta in it but it only puts in four.

This is the cursor

--------------------------------------------------------------
--Openand fetch the data from the cursor
--------------------------------------------------------------

OPEN Dr_DogCommentsSearchCursor

FETCH NEXT FROM Dr_DogCommentsSearchCursor INTO

@dg_serial ,
@dg_name,
@Text_ID,
@date_added,
@owner,
@Text_IN

WHILE @@FETCH_STATUS = 0

BEGIN

--check for nulls

SET @CurrentReg = @dg_serial
IF @CurrentReg = @PreviousReg

BEGIN

UPDATE dbo.tbl_Oz_DR_DOG_COMMENTS_A
SET
dg_serial = @dg_serial,
dg_name = @dg_name,
Text_ID = Text_ID+'**'+CAST(@Text_ID AS VARCHAR),
date_added = date_added+'**'+CAST(@date_added AS VARCHAR),
owner = owner+'**'+CAST(@owner AS VARCHAR),
Text_IN = Text_IN+'**'+CAST(@Text_IN AS VARCHAR)

WHERE dg_serial = @CurrentReg

-- get the next record from the cursor

FETCH NEXT FROM Dr_DogCommentsSearchCursor INTO

@dg_serial ,
@dg_name,
@Text_ID,
@date_added,
@owner,
@Text_IN

SET @PreviousReg = @CurrentReg
END
ELSE

-- do an insert (create new record)

BEGIN

--set the previous reg and insert the data

SET @PreviousReg = @CurrentReg

INSERT INTO dbo.tbl_Oz_DR_DOG_COMMENTS_A
(
dg_serial ,
dg_name,
Text_ID,
date_added,
owner,
Text_IN
)
VALUES
(
@dg_serial ,
@dg_name,
@Text_ID,
@date_added,
@owner,
@Text_IN
)

-- get the next record from the cursor

FETCH NEXT FROM Dr_DogCommentsSearchCursor INTO

@dg_serial ,
@dg_name,
@Text_ID,
@date_added,
@owner,
@Text_IN

END

END

----------------------------------------------------------------------
--Close and deallocate the cursor
----------------------------------------------------------------------

CLOSE Dr_DogCommentsSearchCursor
DEALLOCATE Dr_DogCommentsSearchCursor
GO

This is the output that is incorrect

34306,WEHO,8239**7953**6651**6118,22 Jan 2007**06 Oct 2006**16 Mar 2006**02 Aug 2005,juddyer **rebhart **rebhart **tinscot

it sould be

34306,WEHO ,8239**7953**6651**6118**????**????,22 Jan 2007**06 Oct 2006**16 Mar 2006**02 Aug 2005**????? **?????,juddyer **rebhart **rebhart **tinscot **?????? **???????

Can anyone tell what is wrong with this cursor?

Thanks

Greg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-25 : 04:21:28

Can you explain what you are trying to achieve here?

galbrecht
Starting Member

17 Posts

Posted - 2008-02-25 : 20:11:15

Hi,
I have a table that has dog registrations (dog data). The table has a field for text notes that can be added to a dog. Each dog can have more than one text note and each time you enter a note it gets a text id and the date that the note was added.I have extracted the dog id, text note id, date and the text note it self. So then I end up with a dog that has 6 rows of data (as it has 6 notes). The cursor is to combine the six rows into one with a ** to seperate each text id, date and note in the fields.

Like this

dg_serial,dg_name,ext_ID,date_added,owner, Text_IN
34306,WEHO,8239**7953**6651**6118,22 Jan 2007**06 Oct 2006**16 Mar 2006**02 Aug 2005,juddyer **rebhart **rebhart **tinscot

Does this make sense?

thanks

Greg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-26 : 03:19:46

You can do it like this

DECLARE @txtIDStr varchar(1000),@dateStr varchar(1000),@txtStr varchar(1000)


UPDATE dg
SET @txtIDStr=dg.text_ID = COALESCE(@txtIDStr,'')+ CASE WHEN @txtIDStr IS NOT NULL THEN '**' END+ CAST(txt.textnoteid AS varchar(10)),
@dateStr=dg.date_added= COALESCE(@dateStr,'')+ CASE WHEN @txtIDStr IS NOT NULL THEN '**' END+ CAST(txt.date AS varchar(11)),
@txtStr=dg.text= COALESCE(@txtStr,'')+ CASE WHEN @txtIDStr IS NOT NULL THEN '**' END+ txt.textnote 
FROM dogdata dg
INNER JOIN textnotetable txt
on txt.dogid=dg.dogid

i'm assuming your textnote data is in textnotetable and also you have 1 record per dog in dog data

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources