CODEWITHSUNDEEP
sqlsql-server
Tommy B.
Tommy B.

Reputation: 3639

Looping on rows of a table with a cursor

I'm trying to update a table with demo data, so I decided to build a little stored procedure.

Here it is:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Tommy Bergeron
-- Create date: 2011-07-10
-- Description: This SP updates a Patient's CreateDate row
-- with realistic demo DateTime data.
-- =============================================
CREATE PROCEDURE UpdatePatientCreateDateWithDemoData @PatientID int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON

    DECLARE @IncrementalDateTime DATETIME,
    @BGID int,
    @CreateDate DATETIME

    -- Setting starting datetime    
    SET @IncrementalDateTime = '2012-01-01 10:00 AM'

    -- Declaring cursor
    DECLARE BGCursor CURSOR FOR
        SELECT BGID, CreateDate FROM G2_BloodGlucose WHERE PatientID = @PatientID ORDER BY CreateDate ASC

    -- Opening cursor and starting the loop
    OPEN BGCursor
    FETCH NEXT BGCursor INTO @BGID, @CreateDate
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Updating row with generated DateTime
        UPDATE G2_BloodGlucose SET CreateDate = @IncrementalDateTime WHERE BGID = @BGID

        -- Incrementing DateTime
        SET @IncrementalDateTime = DATEADD(hour, 1, @IncrementalDateTime)

        FETCH NEXT BGCursor INTO @BGID, @CreateDate
    END

    CLOSE BGCursor
    DEALLOCATE BGCursor

END
GO

I built it, to update a DateTime field in a table. So I increment a variable by 1 hour for every row the query finds.

The problem I'm actually having is these errors:

Msg 102, Level 15, State 1, Procedure UpdatePatientCreateDateWithDemoData, Line 27
Incorrect syntax near 'NEXT'.
Msg 102, Level 15, State 1, Procedure UpdatePatientCreateDateWithDemoData, Line 33
Incorrect syntax near 'NEXT'.

I rarely build stored procedure so I'm a bit lost. Would it be possible to have any tips about how to resolve these errors? I looked on several websites to compare my code, but can't find anything useful.

Thanks a lot!

Upvotes: 1

Views: 4061

Answers (2)

Mark Kram
Mark Kram

Reputation: 5832

I think your syntax is incorrect you are missing the FROM Key word

FETCH NEXT FROM BGCursor INTO @BGID, @CreateDate

Upvotes: 1

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32690

Although cursors are probably a tool of the devil, if you want to use them, you need:

 FETCH NEXT FROM BGCursor INTO @BGID, @CreateDate

instead of

 FETCH NEXT BGCursor INTO @BGID, @CreateDate

See the MSDN article for syntax.

Upvotes: 2

Related Questions