CODEWITHSUNDEEP
sqlsql-server
Bob Horn
Bob Horn

Reputation: 34325

Can @@Error be non-Zero on a successful insert?

I'm not sure how to make this happen. We're debugging an issue and we need to know if it's possible for @@Error to be non-zero if the insert succeeds. We have a stored procedure that exits if @Error <> 0. And if we knew the answer to this, that would help. Anyone know?

The code is below. We want to know if it's possible to get to the goto statement if the insert succeeded.

    -- This happened
    insert into Workflow
    (SubID, ProcessID, LineID, ReadTime)
    values
    (@sub_id, @proc_id, @line_id, @read_time)

    set @error = @@Error
    set @insertedWorkflowId = SCOPE_IDENTITY()
    if @error <> 0 
    begin
        set @error_desc = 'insert into tw_workflow'
        goto ERROR_EXIT
    end

    -- This didn't happen
    INSERT INTO Master.WorkflowEventProcessing (WorkflowId, SubId, ReadTime, ProcessId, LineId) VALUES (@insertedWorkflowId, @sub_id, @read_time, @proc_id, @line_id)
    INSERT INTO Master.ProcessLogging (ProcessCode, WorkflowId, SubId, EventTime) VALUES (10, @insertedWorkflowId, @sub_id, GETDATE())

EDIT

Maybe a better way to say what's wrong is this: The first insert happened but the last two didn't. How is that possible? Maybe the last two inserts simply just failed?

Upvotes: 2

Views: 143

Answers (2)

Tim Lehner
Tim Lehner

Reputation: 15251

If this insert succeeds then there will be a non-zero @@rowcount since you're simply using values (rather than a select...where which could "successfully" insert 0 rows). You could use this to write some debug checks in there, or just include it as part of the routine for good.

insert into Workflow
(SubID, ProcessID, LineID, ReadTime)
values
(@sub_id, @proc_id, @line_id, @read_time)

if @@rowcount = 0 or @@error <> 0 -- Problems!

UPDATE

If a trigger fires on insert, an error in the trigger with severity of:

  • < 10: will run without a problem, @@error = 0
  • between 11 and 16: insert will succeed, @@error != 0
  • 17, 18: insert will succeed, execution will halt
  • 19 (with log): insert will succeed, execution will halt
  • > 20 (with log): insert will not succeed, execution will halt

I arrived at this by adding a trigger to the workflow table and testing various values for severity, so I can't readily say this would be the exact case in all environments:

alter trigger workflowtrig on workflow after insert as begin
    raiserror(13032, 20, 1) with log -- with log is necessary for severity > 18
end

Soooo, after that, we have somewhat of an answer to this question:

Can @@Error be non-Zero on a successful insert?

Yes...BUT, I'm not sure if there is another chain of events that could lead to this, and I'm not creative enough to put together the tests to prove such. Hopefully someone else knows for sure.

I know this all isn't a great answer, but it's too big for a comment, and I thought it might help!

Upvotes: 2

Randy Minder
Randy Minder

Reputation: 48502

According to the T-SQL documentation, this should not be possible. You could also wrap this in a try / catch, to attempt to catch most errors.

You might want to also consider the possibility that SCOPE_IDENTITY() is not returning the correct value, or the value you think it is. If you have FKs on the Master.WorkflowEventProcessing and Master.ProcessLogging tables, you could get a FK error attempting to insert into those tables because the value returned from SCOPE_IDENTITY is not correct.

Upvotes: 1

Related Questions