Ignore Error in T-SQL

  • C.K.Shaiju

    SSCarpal Tunnel

    Points: 4228

    Hi,

    How do we ignore an error in T-SQL?

    For e.g.:- The following code throw an error once the @lCounter reach at 15 and will come out. Requirement is it should go till 1000. Even if there are errors in between.

    DECLARE @lCounter SMALLINT

    DECLARE @lError SMALLINT

    SET @lCounter = 0

    WHILE (@lCounter <= 1000 )

    BEGIN

    SELECT CONVERT(VARCHAR, GETDATE(), @lCounter)

    SET @lCounter = @lCounter + 1

    END

    Thanks in advance

    _____________________________________________
    One ounce of practice is more important than tonnes of dreams

  • Sean Lange

    SSC Guru

    Points: 286536

    C.K.Shaiju (9/24/2013)


    Hi,

    How do we ignore an error in T-SQL?

    For e.g.:- The following code throw an error once the @lCounter reach at 15 and will come out. Requirement is it should go till 1000. Even if there are errors in between.

    DECLARE @lCounter SMALLINT

    DECLARE @lError SMALLINT

    SET @lCounter = 0

    WHILE (@lCounter <= 1000 )

    BEGIN

    SELECT CONVERT(VARCHAR, GETDATE(), @lCounter)

    SET @lCounter = @lCounter + 1

    END

    Thanks in advance

    You can't ignore an error. You can however handle it using Try/Catch.

    I hope your code is just for an example of how to force an error because a loop is not very efficient in sql.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Cadavre

    SSC-Forever

    Points: 41690

    C.K.Shaiju (9/24/2013)


    Hi,

    How do we ignore an error in T-SQL?

    For e.g.:- The following code throw an error once the @lCounter reach at 15 and will come out. Requirement is it should go till 1000. Even if there are errors in between.

    DECLARE @lCounter SMALLINT

    DECLARE @lError SMALLINT

    SET @lCounter = 0

    WHILE (@lCounter <= 1000 )

    BEGIN

    SELECT CONVERT(VARCHAR, GETDATE(), @lCounter)

    SET @lCounter = @lCounter + 1

    END

    Thanks in advance

    I can't think of any reason why you'd want to do that. . . but something like this: -

    DECLARE @lCounter SMALLINT, @lError SMALLINT;

    SET @lCounter = 0;

    WHILE (@lCounter <= 1000 )

    BEGIN;

    BEGIN TRY;

    SELECT CONVERT(VARCHAR, GETDATE(), @lCounter);

    SET @lCounter = @lCounter + 1;

    END TRY

    BEGIN CATCH;

    SET @lCounter = @lCounter + 1;

    IF @lCounter >= 1000

    BEGIN;

    RAISERROR('Error',16,1);

    BREAK;

    END;

    END CATCH;

    END;


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • C.K.Shaiju

    SSCarpal Tunnel

    Points: 4228

    Sean LangeI hope your code is just for an example of how to force an error because a loop is not very efficient in sql.

    Yes, An example

    Cadavre


    I can't think of any reason why you'd want to do that

    Would like to know whether we can do that or not. Remember some old vb code with "on error resume next".

    Thanks much for the reply.

    _____________________________________________
    One ounce of practice is more important than tonnes of dreams

  • Sean Lange

    SSC Guru

    Points: 286536

    C.K.Shaiju (9/24/2013)


    Would like to know whether we can do that or not. Remember some old vb code with "on error resume next".

    Thanks much for the reply.

    No you can't simply ignore errors like vb used to do. The closest you can get is the example posted above. 🙂

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • C.K.Shaiju

    SSCarpal Tunnel

    Points: 4228

    No you can't simply ignore errors like vb used to do. The closest you can get is the example posted above. 🙂

    Ok Sean, Thanks.

    _____________________________________________
    One ounce of practice is more important than tonnes of dreams

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply