Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Location
    France
    Posts
    21

    Question Unanswered: catch an error in a fetch loop

    Hi everybody
    I'm looping thrue some records using the FETCH statement.
    something like:

    DECLARE Employee_Cursor CURSOR FOR
    SELECT LastName, FirstName FROM Northwind.dbo.Employees
    OPEN Employee_Cursor
    FETCH NEXT FROM Employee_Cursor
    WHILE @@FETCH_STATUS = 0
    BEGIN
    -- do my SQL stuff here
    FETCH NEXT FROM Employee_Cursor
    END
    CLOSE Employee_Cursor
    DEALLOCATE Employee_Cursor

    I would like to know how to catch an error in my loop.

    Thanks in advance

  2. #2
    Join Date
    Apr 2003
    Location
    SC, USA
    Posts
    51
    There's nothing special about catching errors within a cursor compared to catching errors anywhere else, that I'm aware of.

    Something like the following should work:
    Code:
    IF @@ERROR <>0  
       BEGIN
         SELECT @errno = 30003, @errmsg = 'Insert message here.'
         RAISERROR @errno @errmsg
         --- insert additional statements here ---
       END
    Last edited by loach; 04-29-03 at 17:05.

  3. #3
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Re: catch an error in a fetch loop

    Use @@ERROR - returns the error number of the last SQL statement
    See BOL for more
    Bonne chance!



    Originally posted by djiff
    Hi everybody
    I'm looping thrue some records using the FETCH statement.
    something like:

    DECLARE Employee_Cursor CURSOR FOR
    SELECT LastName, FirstName FROM Northwind.dbo.Employees
    OPEN Employee_Cursor
    FETCH NEXT FROM Employee_Cursor
    WHILE @@FETCH_STATUS = 0
    BEGIN
    -- do my SQL stuff here
    FETCH NEXT FROM Employee_Cursor
    END
    CLOSE Employee_Cursor
    DEALLOCATE Employee_Cursor

    I would like to know how to catch an error in my loop.

    Thanks in advance
    Steve

  4. #4
    Join Date
    Mar 2003
    Location
    France
    Posts
    21

    Re: catch an error in a fetch loop

    just another question:
    if an error occurs in my fetch loop, does the cursor go to next row or does it exit the loop ?

  5. #5
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Re: catch an error in a fetch loop

    Transact-SQL errors cause a statement to be canceled and continue with the next statement in the module (triggers and stored procedures).
    In functions, such errors cause the execution of the function to stop.
    In other words, if you use your fetch loop inside of a stored procedure and an error occurs, then the next statement within the loop will be excuted. If you want to exit from the stored proc. then use the value of @@ERROR:

    IF @@ERROR <> 0 THEN
    BEGIN
    your_code_here
    :
    RETURN
    END

    Originally posted by djiff
    just another question:
    if an error occurs in my fetch loop, does the cursor go to next row or does it exit the loop ?
    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •