CTE - Cursor - Try Catch

• rxm119528

  SSCrazy

  Points: 2960

  More actions

  November 3, 2017 at 11:17 am

  #341187

  Hi,
  I am not able figure out how to catch the data conversion error in the select statement. can you please post your thoughts 

  DECLARE @test-2 TABLE (column1 VARCHAR(256))

  INSERT INTO @test-2
VALUES ('abc')

  BEGIN TRY
    DECLARE @MY_CURSOR CURSOR SET @MY_CURSOR = CURSOR
    FOR
    WITH CTE_1 (column1)
    AS (
        SELECT CAST(column1 AS INT) AS column1
        FROM @test-2
        )
    SELECT *
    FROM CTE_1
    OPTION (MAXRECURSION 0)
        -- to do with cursor
END TRY

  BEGIN CATCH
    print 'Error in the Select Statement'
END CATCH


• Michael L John

  One Orange Chip

  Points: 27060

  More actions

  November 3, 2017 at 11:25 am

  #1966597

  SELECT CAST(column1 AS INT) AS column1 is your error.

  You can't cast 'ABC' to an integer.

  Michael L John
  If you assassinate a DBA, would you pull a trigger?
  To properly post on a forum:
  http://www.sqlservercentral.com/articles/61537/

• rxm119528

  SSCrazy

  Points: 2960

  More actions

  November 3, 2017 at 11:28 am

  #1966598

  Michael L John - Friday, November 3, 2017 11:25 AM

  SELECT CAST(column1 AS INT) AS column1 is your error.

  You can't cast 'ABC' to an integer.

  That's intentional. I want to catch that data conversion error.

• Phil Parkin

  SSC Guru

  Points: 246978

  More actions

  November 3, 2017 at 11:39 am

  #1966601

  This does not get caught at design time. You have to open the cursor to invoke the error handling, as follows:
  DECLARE @test-2 TABLE
(
  column1 VARCHAR(256)
);

  DECLARE @MY_CURSOR CURSOR;
DECLARE @SomeInt INT;

  INSERT INTO @test-2
VALUES
(
  'abc'
);

  BEGIN TRY
  SET @MY_CURSOR = CURSOR FOR WITH CTE_1 (column1)
           AS
           (
            SELECT column1 = CAST(column1 AS INT)
            FROM @Test
           )
  SELECT *
  FROM CTE_1
  OPTION        (MAXRECURSION 0);

    OPEN @MY_CURSOR;

    FETCH NEXT FROM @MY_CURSOR
  INTO @SomeInt;

    WHILE @@FETCH_STATUS = 0
  BEGIN
   FETCH NEXT FROM @MY_CURSOR
   INTO @SomeInt;
  END;

    CLOSE @MY_CURSOR;
  DEALLOCATE @MY_CURSOR;
END TRY
BEGIN CATCH
  PRINT 'Error in the Select Statement';
END CATCH;

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• rxm119528

  SSCrazy

  Points: 2960

  More actions

  November 3, 2017 at 11:56 am

  #1966604

  Phil Parkin - Friday, November 3, 2017 11:39 AM

  This does not get caught at design time. You have to open the cursor to invoke the error handling, as follows:
  DECLARE @test-2 TABLE
  (
    column1 VARCHAR(256)
  );

  DECLARE @MY_CURSOR CURSOR;
  DECLARE @SomeInt INT;

  INSERT INTO @test-2
  VALUES
  (
    'abc'
  );

  BEGIN TRY
    SET @MY_CURSOR = CURSOR FOR WITH CTE_1 (column1)
             AS
             (
              SELECT column1 = CAST(column1 AS INT)
              FROM @Test
             )
    SELECT *
    FROM CTE_1
    OPTION        (MAXRECURSION 0);

    OPEN @MY_CURSOR;

    FETCH NEXT FROM @MY_CURSOR
    INTO @SomeInt;

    WHILE @@FETCH_STATUS = 0
    BEGIN
     FETCH NEXT FROM @MY_CURSOR
     INTO @SomeInt;
    END;

    CLOSE @MY_CURSOR;
  DEALLOCATE @MY_CURSOR;
END TRY
BEGIN CATCH
  PRINT 'Error in the Select Statement';
END CATCH;

  Thanks for the reply. My whole idea was to catch the error column value to a log table. since the data conversion error happens on the fetch, might not able to get that value to a tab

  DECLARE @test-2 TABLE (column1 VARCHAR(256));
DECLARE @ERRORLOG TABLE (column1 VARCHAR(256));
DECLARE @MY_CURSOR CURSOR;
DECLARE @SomeInt INT;

  INSERT INTO @test-2
VALUES ('abc');

  BEGIN TRY
    SET @MY_CURSOR = CURSOR
    FOR
    WITH CTE_1(column1) AS (
            SELECT column1 = CAST(column1 AS INT)
            FROM @test-2
            )

      SELECT *
    FROM CTE_1
    OPTION (MAXRECURSION 0);

      OPEN @MY_CURSOR;

      FETCH NEXT
    FROM @MY_CURSOR
    INTO @SomeInt;

      WHILE @@FETCH_STATUS = 0
    BEGIN
        FETCH NEXT
        FROM @MY_CURSOR
        INTO @SomeInt;
    END;

      CLOSE @MY_CURSOR;

      DEALLOCATE @MY_CURSOR;
END TRY

  BEGIN CATCH
    --intention is to catch the error row to the log table
    INSERT INTO @ERRORLOG
    VALUES (@SomeInt)
END CATCH;

  SELECT *
FROM @ERRORLOG


• drew.allen

  SSC Guru

  Points: 76996

  More actions

  November 3, 2017 at 12:22 pm

  #1966608

  rxm119528 - Friday, November 3, 2017 11:56 AM

  Phil Parkin - Friday, November 3, 2017 11:39 AM

  This does not get caught at design time. You have to open the cursor to invoke the error handling, as follows:
  DECLARE @test-2 TABLE
  (
    column1 VARCHAR(256)
  );

  DECLARE @MY_CURSOR CURSOR;
  DECLARE @SomeInt INT;

  INSERT INTO @test-2
  VALUES
  (
    'abc'
  );

  BEGIN TRY
    SET @MY_CURSOR = CURSOR FOR WITH CTE_1 (column1)
             AS
             (
              SELECT column1 = CAST(column1 AS INT)
              FROM @Test
             )
    SELECT *
    FROM CTE_1
    OPTION        (MAXRECURSION 0);

    OPEN @MY_CURSOR;

    FETCH NEXT FROM @MY_CURSOR
    INTO @SomeInt;

    WHILE @@FETCH_STATUS = 0
    BEGIN
     FETCH NEXT FROM @MY_CURSOR
     INTO @SomeInt;
    END;

    CLOSE @MY_CURSOR;
  DEALLOCATE @MY_CURSOR;
END TRY
BEGIN CATCH
  PRINT 'Error in the Select Statement';
END CATCH;

  Thanks for the reply. My whole idea was to catch the error column value to a log table. since the data conversion error happens on the fetch, might not able to get that value to a tab

  DECLARE @test-2 TABLE (column1 VARCHAR(256));
DECLARE @ERRORLOG TABLE (column1 VARCHAR(256));
DECLARE @MY_CURSOR CURSOR;
DECLARE @SomeInt INT;

  INSERT INTO @test-2
VALUES ('abc');

  BEGIN TRY
    SET @MY_CURSOR = CURSOR
    FOR
    WITH CTE_1(column1) AS (
            SELECT column1 = CAST(column1 AS INT)
            FROM @test-2
            )

      SELECT *
    FROM CTE_1
    OPTION (MAXRECURSION 0);

      OPEN @MY_CURSOR;

      FETCH NEXT
    FROM @MY_CURSOR
    INTO @SomeInt;

      WHILE @@FETCH_STATUS = 0
    BEGIN
        FETCH NEXT
        FROM @MY_CURSOR
        INTO @SomeInt;
    END;

      CLOSE @MY_CURSOR;

      DEALLOCATE @MY_CURSOR;
END TRY

  BEGIN CATCH
    --intention is to catch the error row to the log table
    INSERT INTO @ERRORLOG
    VALUES (@SomeInt)
END CATCH;

  SELECT *
FROM @ERRORLOG


  1) If you get an error CASTing a value to an INT, you're going to get the same error when trying to store that same value in an INT variable.

  2) The process is going to fail when you are defining your cursor, not when you are stepping through your cursor, because your CAST is happening in the cursor definition.

  3) You should not be using a cursor at all.  They are horribly inefficient.  Here is a way to rewrite this query without the cursor.

  DECLARE @test-2 TABLE (column1 VARCHAR(256));
DECLARE @ERRORLOG TABLE (column1 VARCHAR(256));

  INSERT INTO @test-2
VALUES ('abc');

  WITH CTE_1 AS
(
  SELECT
        column1
    ,    column1_int = TRY_CAST(column1 AS INT)
  FROM @test-2
)
INSERT @ERRORLOG(column1)
SELECT column1
FROM CTE_1
WHERE column1_int IS NULL

  SELECT *
FROM @ERRORLOG

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Phil Parkin

  SSC Guru

  Points: 246978

  More actions

  November 3, 2017 at 12:23 pm

  #1966609

  rxm119528 - Friday, November 3, 2017 11:56 AM

  Phil Parkin - Friday, November 3, 2017 11:39 AM

  This does not get caught at design time. You have to open the cursor to invoke the error handling, as follows:
  DECLARE @test-2 TABLE
  (
    column1 VARCHAR(256)
  );

  DECLARE @MY_CURSOR CURSOR;
  DECLARE @SomeInt INT;

  INSERT INTO @test-2
  VALUES
  (
    'abc'
  );

  BEGIN TRY
    SET @MY_CURSOR = CURSOR FOR WITH CTE_1 (column1)
             AS
             (
              SELECT column1 = CAST(column1 AS INT)
              FROM @Test
             )
    SELECT *
    FROM CTE_1
    OPTION        (MAXRECURSION 0);

    OPEN @MY_CURSOR;

    FETCH NEXT FROM @MY_CURSOR
    INTO @SomeInt;

    WHILE @@FETCH_STATUS = 0
    BEGIN
     FETCH NEXT FROM @MY_CURSOR
     INTO @SomeInt;
    END;

    CLOSE @MY_CURSOR;
  DEALLOCATE @MY_CURSOR;
END TRY
BEGIN CATCH
  PRINT 'Error in the Select Statement';
END CATCH;

  Thanks for the reply. My whole idea was to catch the error column value to a log table. since the data conversion error happens on the fetch, might not able to get that value to a tab

  DECLARE @test-2 TABLE (column1 VARCHAR(256));
DECLARE @ERRORLOG TABLE (column1 VARCHAR(256));
DECLARE @MY_CURSOR CURSOR;
DECLARE @SomeInt INT;

  INSERT INTO @test-2
VALUES ('abc');

  BEGIN TRY
    SET @MY_CURSOR = CURSOR
    FOR
    WITH CTE_1(column1) AS (
            SELECT column1 = CAST(column1 AS INT)
            FROM @test-2
            )

      SELECT *
    FROM CTE_1
    OPTION (MAXRECURSION 0);

      OPEN @MY_CURSOR;

      FETCH NEXT
    FROM @MY_CURSOR
    INTO @SomeInt;

      WHILE @@FETCH_STATUS = 0
    BEGIN
        FETCH NEXT
        FROM @MY_CURSOR
        INTO @SomeInt;
    END;

      CLOSE @MY_CURSOR;

      DEALLOCATE @MY_CURSOR;
END TRY

  BEGIN CATCH
    --intention is to catch the error row to the log table
    INSERT INTO @ERRORLOG
    VALUES (@SomeInt)
END CATCH;

  SELECT *
FROM @ERRORLOG


  OK, but as your cursor is selecting from an existing table, surely you already know the source column data types and therefore you can ensure that the error never occurs?

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Phil Parkin

  SSC Guru

  Points: 246978

  More actions

  November 3, 2017 at 12:27 pm

  #1966610

  Alternatively, why not something like
  IF TRY_CAST(Column1 as INT) is NULL
  BEGIN
  --Write to error log
  END?
  Though you would have to do this as part of the FETCH loop, not in the CTE.

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• rxm119528

  SSCrazy

  Points: 2960

  More actions

  November 3, 2017 at 12:32 pm

  #1966611

  Phil Parkin - Friday, November 3, 2017 12:27 PM

  Alternatively, why not something like
  IF TRY_CAST(Column1 as INT) is NULL
  BEGIN
  --Write to error log
  END?
  Though you would have to do this as part of the FETCH loop, not in the CTE.

  Yes. I agree with you. Source data can be verified well before the start of the process.
  I was working on this and never had a though of this scenario. So wanted to understand the limitations of cursor  and try catch combination.

  Thanks everyone.

• rxm119528

  SSCrazy

  Points: 2960

  More actions

  November 3, 2017 at 12:34 pm

  #1966612

  drew.allen - Friday, November 3, 2017 12:22 PM

  rxm119528 - Friday, November 3, 2017 11:56 AM

  Phil Parkin - Friday, November 3, 2017 11:39 AM

  This does not get caught at design time. You have to open the cursor to invoke the error handling, as follows:
  DECLARE @test-2 TABLE
  (
    column1 VARCHAR(256)
  );

  DECLARE @MY_CURSOR CURSOR;
  DECLARE @SomeInt INT;

  INSERT INTO @test-2
  VALUES
  (
    'abc'
  );

  BEGIN TRY
    SET @MY_CURSOR = CURSOR FOR WITH CTE_1 (column1)
             AS
             (
              SELECT column1 = CAST(column1 AS INT)
              FROM @Test
             )
    SELECT *
    FROM CTE_1
    OPTION        (MAXRECURSION 0);

    OPEN @MY_CURSOR;

    FETCH NEXT FROM @MY_CURSOR
    INTO @SomeInt;

    WHILE @@FETCH_STATUS = 0
    BEGIN
     FETCH NEXT FROM @MY_CURSOR
     INTO @SomeInt;
    END;

    CLOSE @MY_CURSOR;
  DEALLOCATE @MY_CURSOR;
END TRY
BEGIN CATCH
  PRINT 'Error in the Select Statement';
END CATCH;

  Thanks for the reply. My whole idea was to catch the error column value to a log table. since the data conversion error happens on the fetch, might not able to get that value to a tab

  DECLARE @test-2 TABLE (column1 VARCHAR(256));
DECLARE @ERRORLOG TABLE (column1 VARCHAR(256));
DECLARE @MY_CURSOR CURSOR;
DECLARE @SomeInt INT;

  INSERT INTO @test-2
VALUES ('abc');

  BEGIN TRY
    SET @MY_CURSOR = CURSOR
    FOR
    WITH CTE_1(column1) AS (
            SELECT column1 = CAST(column1 AS INT)
            FROM @test-2
            )

      SELECT *
    FROM CTE_1
    OPTION (MAXRECURSION 0);

      OPEN @MY_CURSOR;

      FETCH NEXT
    FROM @MY_CURSOR
    INTO @SomeInt;

      WHILE @@FETCH_STATUS = 0
    BEGIN
        FETCH NEXT
        FROM @MY_CURSOR
        INTO @SomeInt;
    END;

      CLOSE @MY_CURSOR;

      DEALLOCATE @MY_CURSOR;
END TRY

  BEGIN CATCH
    --intention is to catch the error row to the log table
    INSERT INTO @ERRORLOG
    VALUES (@SomeInt)
END CATCH;

  SELECT *
FROM @ERRORLOG


  1) If you get an error CASTing a value to an INT, you're going to get the same error when trying to store that same value in an INT variable.

  2) The process is going to fail when you are defining your cursor, not when you are stepping through your cursor, because your CAST is happening in the cursor definition.

  3) You should not be using a cursor at all.  They are horribly inefficient.  Here is a way to rewrite this query without the cursor.

  DECLARE @test-2 TABLE (column1 VARCHAR(256));
DECLARE @ERRORLOG TABLE (column1 VARCHAR(256));

  INSERT INTO @test-2
VALUES ('abc');

  WITH CTE_1 AS
(
  SELECT
        column1
    ,    column1_int = TRY_CAST(column1 AS INT)
  FROM @test-2
)
INSERT @ERRORLOG(column1)
SELECT column1
FROM CTE_1
WHERE column1_int IS NULL

  SELECT *
FROM @ERRORLOG

  Drew

  Thank you. I do agree that cursors are bad but just trying to understand the cursor and try catch combination.

• drew.allen

  SSC Guru

  Points: 76996

  More actions

  November 3, 2017 at 1:41 pm

  #1966619

  rxm119528 - Friday, November 3, 2017 12:34 PM

  Thank you. I do agree that cursors are bad but just trying to understand the cursor and try catch combination.

  WHY?!?!?!  You would be much better off spending time getting rid of the cursor than trying to understand how it interacts with TRY/CATCH.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 3, 2017 at 4:17 pm

  #1966642

  First, I must agree, find a better way than using a cursor.  As an exercise, you would need something like this:

  declare
  @ErrorNumber int,
  @ErrorLine int,
  @ErrorMessage nvarchar(4000),
  @ErrorProcedure nvarchar(128),
  @ErrorSeverity int,
  @ErrorState int,
  @SQLRowsAffected int = 0,
  @GETDATE_STRING varchar(100),
  @FetchStatus int;

  DECLARE @test-2 TABLE
  (
    column1 VARCHAR(256)
  );

  DECLARE @MY_CURSOR CURSOR;
  DECLARE @SomeInt INT;

  INSERT INTO @test-2
  VALUES ('1'),('abc'),('2'),('def');

  
  SET @MY_CURSOR = CURSOR FOR WITH CTE_1 (column1)
            AS
            (
            SELECT column1 = CAST(column1 AS INT)
            FROM @test-2
            )
  SELECT *
  FROM CTE_1
  --OPTION        (MAXRECURSION 0) << This is not needed
  ;

  OPEN @MY_CURSOR;

  BEGIN TRY

    FETCH NEXT FROM @MY_CURSOR
    INTO @SomeInt;

    WHILE @@FETCH_STATUS = 0
    BEGIN
   
      SELECT CAST(@SomeInt AS INT);

      BEGIN TRY
     
      FETCH NEXT FROM @MY_CURSOR
      INTO @SomeInt;
      END TRY
      BEGIN CATCH
            SELECT @FetchStatus = @@FETCH_STATUS;
            select
              @ErrorNumber = ERROR_NUMBER(),
              @ErrorLine = ERROR_LINE(),
              @ErrorMessage = ERROR_MESSAGE(),
              @ErrorProcedure = ERROR_PROCEDURE(),
              @ErrorSeverity = ERROR_SEVERITY(),
              @ErrorState = ERROR_STATE();

              SELECT 'inner try', @ErrorNumber, @ErrorMessage, @ErrorSeverity,@FetchStatus;

      END CATCH;
    END;

  END TRY
  BEGIN CATCH
        SELECT @FetchStatus = @@FETCH_STATUS;
        select
          @ErrorNumber = ERROR_NUMBER(),
          @ErrorLine = ERROR_LINE(),
          @ErrorMessage = ERROR_MESSAGE(),
          @ErrorProcedure = ERROR_PROCEDURE(),
          @ErrorSeverity = ERROR_SEVERITY(),
          @ErrorState = ERROR_STATE();

          SELECT 'outer try', @ErrorNumber, @ErrorMessage, @ErrorSeverity,@FetchStatus;

  END CATCH;

  CLOSE @MY_CURSOR;
  DEALLOCATE @MY_CURSOR;
  

• Jeff Moden

  SSC Guru

  Points: 1004403

  More actions

  November 5, 2017 at 7:08 pm

  #1966725

  drew.allen - Friday, November 3, 2017 1:41 PM

  rxm119528 - Friday, November 3, 2017 12:34 PM

  Thank you. I do agree that cursors are bad but just trying to understand the cursor and try catch combination.

  WHY?!?!?!  You would be much better off spending time getting rid of the cursor than trying to understand how it interacts with TRY/CATCH.

  Drew

  To know thy enemy. 😉  Besides, you DO have to know how to use cursors to know WHEN to use cursors or not.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)