TRY CATCH in SQL QUERY

I have a MS SQL 2005 server, I have a FOR WHILE

Statement and it works properly on my database and do some stuff on all tables.

I got a problem, for some unknown reason sometimes it get a problem on 1 field, on 1 table,

I want it to bypass and continue execution and not stop at error...

Something like try catch or ON ERROR RESUME NEXT

Is it a case in SQL?

Please advice

Thanks
LVL 17
CSecurityAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
you can use  'continue '  statement
declare @tab table ( i int identity, div int  ) 
insert into @tab select '10' 
insert into @tab select '20' 
insert into @tab select '0' 
insert into @tab select '30' 
insert into @tab select '40' 
 
declare @i int ,@div int 
set @i = 1 
while @i  < 6 
begin 
	select @Div = div from @Tab where i = @i 
	begin try 
		select 1000/@div 
	end try 
	begin catch		
		set @i = @i +1
		continue;
	end catch 
	set @i = @i +1 
end 

Open in new window

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Within your WHILE clause try using the below approach:
while (a <100)
-- your operation
select * from table

-- capture error code
if @@error <> 0
--  log into some tables

a = a + 1;

By this approach, you will be able to run your statements without exiting out of the other processes.
0
 
rseabirdCommented:
I suppose you mean T-SQL. and then, yes you can. See the code snippet for the syntax.
You can also check http://blog.sqlauthority.com/2007/04/11/sql-server-2005-explanation-of-trycatch-and-error-handling/ or http://msdn.microsoft.com/en-us/library/ms179296.aspx

BEGIN TRY
{ sql_statement |
statement_block }
END TRY
BEGIN CATCH
{ sql_statement |
statement_block }
END CATCH

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.