April 25, 2007 at 4:29 am
How To trap error in SQL Server?
E.g :
While executing a procedure an error comes at first part of the procedure. So when error comes i don't want to execute first part, quit from there then second part of the procedure should be executed.
Normally when error comes subsequent statement will not be executed.How to handle this error.
Regards
Ashik Nihal M.s
April 25, 2007 at 6:36 am
Do a BOL lookup for @@ERROR
April 27, 2007 at 4:20 pm
I understand that it may be a BOL question, but why not give a short answer then refer to the docs. I think it's rather rude just to answer RTFM without at least a courteous salutation.
Scott
April 29, 2007 at 1:54 pm
Below are two quick examples. The short answer is that if you are using SQL Server 2000 you need to user @@error, if you are in SQL 2005 you can use @@Error or a try catch block.
@@error must be called immediately after the statement executed or if you executing a stored procedure you set your variable holding the error results = to stored procedure.
For example:
select @X + @Y
select @myerror = @@Error
or
execute @myerror = <stored procedure name)
Below are two complete example. copy them into QA for SSMS and give them a try.
Hope this helps,
Mike
'****** SQL Server 2000/2005 using @@Error'
declare
@myerror bit --0 success, 1 -- failure
declare
@x int
declare
@y int
declare
@Results int
set
@x = 2
set
@y = 0
set
@myerror =0
if
@myerror = 0
begin
--Continue doing part
--1st part of step 1
set @Results = @x / @y
set @myError = @@error -- Must be called as the very next statment
--2nd part of step 1
set @Results = @x + @y
end
--Step 2
print 'Part 2 results: ' + cast(@Results as varchar(10))
--SQL 2005 using try catch block
'****** SQL Server 2005 using try catch'
set
@x = 2
set
@y = 0
begin
try
--1st part of step 1
set @Results = @x / @y
set @myError = @@error -- Must be called as the very next statment
--2nd part of step 1
set @Results = @x + @y
end
try
begin
catch
Print 'Error occured in first part of procedure'
end
catch
begin
try
--Step 2
print 'Part 2 results: ' + cast(@Results as varchar(10))
end
try
begin
catch
print 'error occured in 2nd part of procedure'
end
catch
April 29, 2007 at 3:47 pm
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply