Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trap Error Expand / Collapse
Author
Message
Posted Wednesday, April 25, 2007 4:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 9, 2008 5:56 AM
Points: 5, Visits: 29

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

 

 

 

 

 

 

Post #360777
Posted Wednesday, April 25, 2007 6:36 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, October 17, 2010 4:18 PM
Points: 360, Visits: 168
Do a BOL lookup for @@ERROR
Post #360803
Posted Friday, April 27, 2007 4:20 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 5:44 PM
Points: 148, Visits: 24
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



Kindest Regards,

Scott Beckstead


"We cannot defend freedom abroad by abandoning it here at home!"
Edward R. Murrow

scottbeckstead.com

Post #361713
Posted Sunday, April 29, 2007 1:54 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, November 15, 2014 1:22 PM
Points: 175, Visits: 33

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

 

print '****** 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

print '****** 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




Post #361833
Posted Sunday, April 29, 2007 3:47 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 5:44 PM
Points: 148, Visits: 24
Thank you Mike. Very Helpful. Though I did RTFM and I wasn't the original requester Courtesy goes a long way.





Kindest Regards,

Scott Beckstead


"We cannot defend freedom abroad by abandoning it here at home!"
Edward R. Murrow

scottbeckstead.com

Post #361838
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse