SQL SERVER T-SQL EXCEPTIONS HANDLING IN WHILE LOOP

SQL SERVER EXCEPTIONS HANDLING IN WHILE LOOP

FOR EX:  tinyint datatype holds values  from 0 to 255. if value exceeds 255, throws an exception saying "Arithmetic overflow error for data type tinyint, value = 256.' to data type int"  and resets the value to 0,

In order to catch such type of exceptions.

declare @count tinyint=0;

begin try
while @count <= 255
begin

print @count;


set @count = @count+1;
end
end try
 

begin catch

print @@error+ERROR_MESSAGE()
end catch

Note: If exceptions are not trapped it will be in an infinite loop, because @counter will be reset to 0.

OUTPUT

0
1

..

..

. ..

252
253
254
255
Msg 245, Level 16, State 1, Line 16
Conversion failed when converting the nvarchar value 'Arithmetic overflow error for data type tinyint, value = 256.' to data type int.

TAGS: BEGIN TRY in WHILE LOOP,Catching exceptions in T-SQL, SQL Server exceptions,END TRY,  BEGIN CATCH in SQL SERVER, END CATCH IN SQL SERVER,

SQL SERVER T-SQL EXCEPTIONS,