Error handling when one field value is bad

Here's an example of what I'm playing with:

update TestTable

set IntValue = Cast(StringValue, as int)

flag =

BEGIN TRY

0

END TRY

BEGIN CATCH

1

END CATCH

I'm trying to take a string value holding an integer and populate an integer field with that value. But, every so often, the string value does not contain an integer (e.g. '13.9" or "CA"). That error would cause the entire column UPDATE to be rolled back. i'm trying to get around it with some kind of "TRY CATCH" construct but I don't believe it can work.

What would you suggest?

Barkingdog

No, you are right this won′t work. For me, the best Exception handling is to prevent exceptions, in your case check the value for being numeric, there is a function ISNUMERIC which can do that for you. But be careful with that function, because it checks for currency numeric only, so the value "$" which is part of a currency expression will also evaluate to true.

See more details on the site: http://www.aspfaq.com/show.asp?id=2390

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Error Handling problem

I have a strange behavior when executing a CLR SP:

The SP contains the following test code:

try {
int.Parse("");
}
catch (Exception ex) {
try {
command.CommandText = "raiserror (100003, 16, 1, 'parse error') with seterror";
pipe.ExecuteAndSend(command);
}
catch {
return;
}
}

Here the SQL I use to test it

decare @.e int
begin try
exec MySP
set @.e = @.@.error
print @.e
print 'phew!'
end try
begin catch
print error_message()
end catch
print 'done'

What I expect is that, since MySP throws an error, the catch block is executed and the error message is printed out.

What happens is that
@.@.error contains the correct error code 100003
the catch block does not executes
Here the output of SSMS:

Msg 100003, Level 16, State 1, Line 1
"invalid operation: parse error"
100003
phew!
done

Any Idea of why @.@.error is set but the catch block does not executes ?

just a final note: if do not use the try/catch block in the SP and let the exception be caught into the server engine, the catch block on the T-SQL is correctly executed (and it is my current solution, sigh !!!)Hi,

see this connect bug here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=251376

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Thanks from providing the link.
I'm skeptical they will fix it less time than a couple of years.

Error handling in Stored procedure

I have a table called 'Testtable'
It has two columns
age int , notnull
name varhar(10), notnull
I wrote a stored procedure as follows to trap the error.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER procedure sperror
@.a int,
@.n varchar(10),
@.err int,
@.ro int
As
insert into testtable(name,age) values (@.n,@.a)
select @.err = @.@.error, @.ro = @.@.rowcount
print @.ro
if @.err <> 0
print 'Error occorrred in stored procedure ' + str(@.err)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I was unable to get the message that I bold, when I execute it from query analyzer.
Regards,
Bhuwan
Regards,
Bhuwan
http://www.sommarskog.se/error-handling-II.html
"Bhuwan Bhaskar" <kxxx@.gmail.com> wrote in message news:OiwnD4fnIHA.1768@.TK2MSFTNGP05.phx.gbl...
I have a table called 'Testtable'
It has two columns
age int , notnull
name varhar(10), notnull
I wrote a stored procedure as follows to trap the error.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER procedure sperror
@.a int,
@.n varchar(10),
@.err int,
@.ro int
As
insert into testtable(name,age) values (@.n,@.a)
select @.err = @.@.error, @.ro = @.@.rowcount
print @.ro
if @.err <> 0
print 'Error occorrred in stored procedure ' + str(@.err)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I was unable to get the message that I bold, when I execute it from query analyzer.
Regards,
Bhuwan
Regards,
Bhuwan