Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003

    Unanswered: trap convertion errors

    I want to trap convertion errors in a proper way, that is:

    declare @t table ( val varchar(50) null, nval int, dval int)
    declare @f float, @d datetime
    declare @vf int, @vd int

    set @f = convert (float,'123')
    if @@error=0 set @vf = 1 else set @vf = 0
    set @d = convert(datetime,'20031201',112)
    if @@error=0 set @vd = 1 else set @vd = 0
    insert into @t
    select '1', @vf, @vd

    set @f = convert (float,'123,f')
    if @@error=0 set @vf = 1 else set @vf = 0
    set @d = convert(datetime,'20031233',112)
    if @@error=0 set @vd = 1 else set @vd = 0
    insert into @t
    select '2', @vf, @vd

    select * from @t

    what I expect to get in table @t is the following:

    val nval dval
    1 1 1
    2 0 0

    what I really get is:

    Servidor: mensaje 8114, nivel 16, estado 5, línea 14
    Error converting data type varchar to float.

    at first convertion error (in row 2)

    any idea?

  2. #2
    Join Date
    Jul 2002
    Village, MD

    A batch is a group of one or more Transact-SQL statements sent at one time from an application to Microsoft® SQL Server™ for execution. SQL Server compiles the statements of a batch into a single executable unit, called an execution plan. The statements in the execution plan are then executed one at a time.

    A compile error, such as a syntax error, prevents the compilation of the execution plan, so none of the statements in the batch are executed.

    A run-time error, such as an arithmetic overflow or a constraint violation, has one of two effects:
    -Most run-time errors stop the current statement and the statements that follow it in the batch.
    -A few run-time errors, such as constraint violations, stop only the current statement. All the remaining statements in the batch are executed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts