September 30, 2008 at 1:49 pm
I used @@error to detect the result of the update statement. In my script, when @@error is not equal to 0, goto statement will be fired. I tested my script for input wrong data type. However, the script couldn't run the goto statement; it shows error message on result panel.
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'char' to data type int.
Does anyone can tell me why GOTO statement failed to run? My script is follows.
Declare @ErrorCounter int
Update dbo.Table_1 set changeUser='char' where id=1
--print 'error='+cast(@@error as varchar)
set @ErrorCounter=@@error
if (@ErrorCounter<>0) GOTO Roll_Back
else print 'updated'
Roll_Back:
if (@ErrorCounter<>0)
begin
print 'RollBack tran'
end
September 30, 2008 at 5:43 pm
It never gets that far. The error you're seeing happens before you even get to the GOTO.
Since you're in 2005 - you REALLY should try the NEW way to handle errors: Try/ Catch.
declare @errstring varchar(250)
BEGIN TRY
Update dbo.Table_1 set changeUser='char' where id=1
Print 'updated'
END TRY
BEGIN CATCH
set @errstring= 'RollBack tran'
+cast(error_number() as varchar(20)+' -- '
+error_message() + ' -- '
+error_severity() + ' -- '
+error_procedure()
print @errstring
END CATCH
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 1, 2008 at 8:17 am
Hi,
Could you send us the exact error you are getting when run the update query alone?.
Thanks,
Karthik
October 3, 2008 at 7:35 pm
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'char' to data type int.
October 3, 2008 at 7:37 pm
If I used sql2000, what should I do?
By the way, would you tell me what kind of error will be make @@error=1?
October 3, 2008 at 8:26 pm
As it is - that's a batch-terminating error. Meaning - it pretty much drops out right then and there and never goes past it.
In 2000, the only way I know how to get past it is to push that stuff into a lower context, by wrapping it in an EXEC statement.
Declare @ErrorCounter int
declare @sql varchar(4000)
set @sql='Update dbo.Table_1 set changeUser=''char'' where id=1'
exec(@sql)
--print 'error='+cast(@@error as varchar)
set @ErrorCounter=@@error
if (@ErrorCounter<>0) GOTO Roll_Back
else print 'updated'
Roll_Back:
if (@ErrorCounter<>0)
begin
print 'RollBack tran'
end
You STILL get an error, but the GOTO happens, so you've managed to give yourself an opportunity to handle the error.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 3, 2008 at 8:28 pm
On the other hand - you're apparently trying to put a character value into a column that is an integer type, so that will NEVER happen. Avoiding that would allow you not to have to handle the error.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 21, 2008 at 11:02 am
Thanks
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply