Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 747
  • Last Modified:

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
0
CSecurity
Asked:
CSecurity
1 Solution
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now