July 24, 2001 at 6:44 am
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.
July 24, 2001 at 8:25 am
Check out the @@Error variable to handle errors.
July 24, 2001 at 11:10 pm
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"
December 8, 2002 at 3:31 pm
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