Error with cursor syntax. - 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)

Error with cursor syntax.

Author

Topic

tpayne
Starting Member

18 Posts

Posted - 2008-10-20 : 15:52:33

Here is my stored proc.
BEGIN TRY
BEGIN TRANSACTION

/*Update the Group record*/
UPDATE tblComptencyCategories
SET
CompCategoryName = @CompCategoryName,
CompCategoryID = @NewCompCategoryID
WHERE
CompCategoryID = @OldCompCategoryID
AND IDRIterationID = @IDRIterationID

/*Now change all the child id's to reflect the new id*/
DECLARE @ParentID VARCHAR(50)
DECLARE @ChildID VARCHAR(50)

SET @ParentID = LEFT(@NewCompCategoryID,1)

DECLARE @AccountibilityCursor CURSOR FOR
SELECT ComptencySubCategoryID
FROM dbo.tblCompetencySubCategories
WHERE IDRIterationID = @IDRIterationID

OPEN @AccountibilityCursor
FETCH NEXT FROM @AccountibilityCursor INTO @ChildID
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @tmp VARCHAR(50)
SET @tmp = @ParentID + RIGHT(@ChildID,2)

UPDATE dbo.tblCompetencySubCategories
SET
CompSubCategoryID = @tmp
WHERE
ComptencySubCategoryID = @ChildID
AND IDRIterationID = @IDRIterationID

FETCH NEXT FROM @AccountibilityCursor INTO @ChildID
END

CLOSE @AccountibilityCursor
DEALLOCATE @AccountibilityCursor

COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK

-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

Any idea why i'm getting these errors?

Msg 156, Level 15, State 1, Procedure spUpdateCompetencyCategories, Line 43
Incorrect syntax near the keyword 'FOR'.
Msg 102, Level 15, State 1, Procedure spUpdateCompetencyCategories, Line 79
Incorrect syntax near 'END'.

thanks.

tpayne
Starting Member

18 Posts

Posted - 2008-10-20 : 16:19:42

I figured out that you shouldn't use an @ for cursor variable name.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-20 : 16:35:42

Why are you using a cursor? Can't you update all the childIDs in one statement rather than one at a time?


              update csc set
                     CompSubCategoryID = @ParentID + RIGHT(ComptencySubCategoryID, 2)
              from   dbo.tblCompetencySubCategories csc
              where  IDRIterationID = @IDRIterationID

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources