Trapping an Error

  • 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.

  • Check out the @@Error variable to handle errors.

  • 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"

  • 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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply