Try / catch info needed.

Question

Try / catch info needed.

kgunnarsson

Ten Centuries

Points: 1218
More actions
March 10, 2008 at 4:10 am

#184869

it' may well be that this is just my Monday morning but i thought i could handle pk exceptions and still get the source record in the catch statement..
take a look at the example.
--drop table #source
--drop table #testCatch
create table #source(a int , b int , c varchar(10) )
go
insert into #source(a,b,c) values (1,2,'a')
insert into #source(a,b,c) values (1,2,'a')
insert into #source(a,b,c) values (1,3,'a')
go
create table #testCatch (a int primary key , b int , c varchar(10))
go
begin try
insert #testCatch
select a,b,c from #source s
end try
begin catch
if (error_number()=2627)
begin
/* My source can’t be bound in the catch segment of the code
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "s.b" could not be bound.*/
select s.b
print error_message()
end
end catch
Am I wrong in the assumption that this is an possibility
kgunnarsson
Mcitp Database Developer.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

The "s" table alias is restricted to your insert statement, and does not go into the begin statement or higher. E.g. you could have a second insert/select statement in the try block that could use the same name for a table alias.

begin try

insert #testCatch

select a,b,c from #source s

insert #testCatch

select a,b,c from #source s

insert #testCatch

select a,b,c from #source s

end try

...

Because of this you cannot use this alias in the catch block either. In the above example, which "s" would you refer to? You could still say "a,b,c from #source" in the catch block, and you will get the offending rows too.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

kgunnarsson Ten Centuries Points: 1218 More actions · Answer 2

ok.. but that kind of defeats the propose where you want to work with the current row in the select part insert statement.

That is for each line in the select statement you want to handle the current error row.

for example if you want to try to insert an row but update in case of an pk error.

but i understand what you mean.

The only solution I have found is to exclude all records already in the dest table in the select statement and update them separately

Thax for the info much appreciated

kgunnarsson
Mcitp Database Developer.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

kgunnarsson (3/10/2008)
ok.. but that kind of defeats the propose where you want to work with the current row in the select part insert statement.
That is for each line in the select statement you want to handle the current error row.
for example if you want to try to insert an row but update in case of an pk error.
but i understand what you mean.
The only solution I have found is to exclude all records already in the dest table in the select statement and update them separately
Thax for the info much appreciated

I've always been amazed that people even think of using Try/Catch in SQL Server. Think about it... it's allowing the code to be controlled by errors instead of known facts.

The solution highlighted in bold in the above quote is actually the right way to do a merge... it will likely be faster than Try/Catch, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kgunnarsson Ten Centuries Points: 1218 More actions · Answer 4

Point taken.

as i started out with... monday morning 🙂

kgunnarsson
Mcitp Database Developer.