Trapping an Error

• pattikay

  Old Hand

  Points: 349

  More actions

  July 24, 2001 at 6:44 am

  #77650

  Is it possible to trap an error that occurs when a statement is executed dynamically i.e

  set @dbexists = 'select dbid from '+@servername+'.master.dbo.sysdatabases where name = "'+@dbname+'"'

  if for one reason or the other an error occurs ,maybe because the server is not available or for any other reason during the statements execution , how do i trap the error and move to the next statement in the stored procedure?

  Thanks in advance.

• jwiner

  SSCrazy

  Points: 2241

  More actions

  July 24, 2001 at 8:25 am

  #417247

  Check out the @@Error variable to handle errors.

• ckempste

  SSCoach

  Points: 17983

  More actions

  July 24, 2001 at 11:10 pm

  #417268

  Hi

  Yep, this is a problem alright. Check the EXECUTE function to run your dynamic SQL, there is a return value that you can check which may help you. Also the WHENEVER clause is for C only, not t-sql. Use

  <statement here>

  set @vmyerrorcatch = @@ERROR

  dont try and use

  if @@ERROR > 1 begin

  <etc>

  as ive had all sorts of issues with it not picking up on the error.

  
  Chris Kempster
  www.chriskempster.com
  Author of "SQL Server Backup, Recovery & Troubleshooting"
  Author of "SQL Server 2k for the Oracle DBA"

• don1941

  SSCarpal Tunnel

  Points: 4183

  More actions

  December 8, 2002 at 3:31 pm

  #443610

  The best form of error handling is prevention. Short of that, you can check @@ERROR and take action based on the error code that is raised from your statement. Just be aware the EVERY T-SQL statement resets @@ERROR. you need to use the following

  declare @myError int, @MyRowCount int

  < some action >

  select @MyError = @@Error, @MyRowCount = @@ROWCOUNT

  and then use the @My... variable for all further tests.

  Note that severe errors terminate your stored proc or batch immediately and cannot be handled.

  You can influence how errors are handled using SET XACT_ABORT in your proc.