Ignore Error in T-SQL

Question

Ignore Error in T-SQL

C.K.Shaiju

SSCarpal Tunnel

Points: 4228
More actions
September 24, 2013 at 8:12 am

#283622

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

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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: 286590 More actions · Answer 4

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. 🙂