error in batch sql server

Question

For list1, select 'ok' will be run even select 1/0 generate an error. for list 2 select 'ok' will not be run since update fail. Both list generate error of level 16 but why there are such difference?

--1
select 1/0  
select 'ok'

--2
create table #t (a int)
insert into #t values(1)

update #t set b = 99
select 'ok'

Martin Smith · Accepted Answer · 2012-10-02 11:45:00Z

3

update #t set b = 99 is a compile time error.

The batch is compiled and as #t does not exist that statement is subject to a deferred compile. It is impossible to even generate a plan for this statement to update a non existent column so there is a compile error on that statement.

Runtime compile errors abort the scope.

It is not always intuitive what effect a runtime error will have (abort the statement, scope, batch or connection). See What Happens when an Error Occurs? for some examples.

edited Oct 2, 2012 at 11:45

answered Oct 2, 2012 at 11:38

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

thotwielder Over a year ago

for list2 there is no compile error either. both lists generate error when running the codes. But the link in the reply seems nice. I am reading it. Cheers.

Martin Smith Over a year ago

@thotwielder - The error is a compile time error. You can tell that by creating the temporary table then trying to generate an estimated execution plan for the statement update #t set b = 99. I presume you missed what I wrote about deferred compile.

thotwielder Over a year ago

Yes, I was wondering why sql server will continue after the error in list 1. Now it seems makes sense. Thank you.

Collectives™ on Stack Overflow

error in batch sql server

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related