Thank you all for tips and tricks. We will do one of the following:

1. Move all data verifications to .NET and only call the stored procedure with perfect data.

2. Split...

bitbucket-25253, it is impossible to return to a saved transaction state if SQL Server has decided that the transaction is Doomed/Uncommitable. That is the issue.

Peter, you need to check XACT_STATE since -1 means the transaction is Doomed and nothing can be done.

wschampheleer, does not solve this issue. What he does is have one transaction...

As Lowell was saying, everything works fine as long as you are not in a transaction. Then the try/catch works just fine... but inside a transaction, things like this makes...

I know. That was the point of the post... that Try/Catch cannot be used to verify incoming data.

/Ricky

The best I can come up with is:

BEGIN TRANSACTION

DECLARE @STR NVARCHAR(MAX)

DECLARE @amount DECIMAL(18,2)

SELECT @STR = '13,00'

BEGIN TRY

IF ISNUMERIC(@str)=1

BEGIN

SELECT @amount = CONVERT(DECIMAL(18,2), @STR)

END

ELSE

BEGIN

SELECT @amount =...

I tried a new approach, where I started a nested transaction to solve the problem but since SQL Server does not support rollback of inner transactions without rolling back the...

Does that mean that nested transactions has no use what so ever?

Thank you for replying.

I cannot set the transaction there. In my live example, the transaction is much larger, it handles wether a whole file of data should be imported or...