October 22, 2008 at 10:31 am
Does this design pattern work in TSQL on SQL 2005?
INSERT into sometable
IF @@error <> 0 –expect 2627 unique constraint violation
BEGIN
UPDATE sometable
END
Continue doing more stuff…
Here, the code is trying to do an insert, while not knowing if the row already exists in the table. If it does exist, it expects an @@error code of 2627 (unique key constraint). If it gets it, then it tries to update the row instead.
The reason I ask is that this pattern appears in the one of our stored procedure. Somebody wrote it that way, but it doesn’t appear to work now. Instead, we get an exception thrown back to the java code immediately when the insert call fails.
Do you think this code should work? If so, can you think of a reason why it is not working for us?
Thanks
Dev
October 22, 2008 at 11:01 am
if @@error ...then...else
will not work, because a PK error is severity 16 or above, so the code execution stops on the error.
i believe if you redo it as a try...catch it will work .
example:
create table test(testid int primary key,othercol varchar(30))
begin try
insert into test(testid,othercol) select 1,'First'
insert into test(testid,othercol) select 1,'Second'
end try
begin Catch
update test set othercol='Catch'
end catch
select * from test
Lowell
October 22, 2008 at 11:24 am
There are quite a few alternative ways to resolve this issue. But we are trying to figure out how it used to work before in SQL 2000. Is there any specific option to be set to reset the Severity level or not to abort the statement
We are planning to rewrite the code using TRY & CATCH
Dev
October 22, 2008 at 11:38 am
oops: a PK error is level 14, not 16, so it CAN be managed with @@error:
using the same tablein my first example, here's another example:
--error level 14!!
insert into test(testid,othercol) select 1,'First'
insert into test(testid,othercol) select 1,'Second'
if @@error <> 0
begin
print 'oops error found'
update test set othercol='if error'
end
Lowell
October 22, 2008 at 11:52 am
Hi Lowell,
This is where we are confused. Try running your code and you would get an error
--error level 14!!
insert into test(testid,othercol) select 1,'First'
insert into test(testid,othercol) select 1,'Second'
if @@error <> 0
begin
print 'oops error found'
update test set othercol='if error'
end
but if you change this code with a local variable, it works.
Declare @err int
insert into test(testid,othercol) select 1,'First'
insert into test(testid,othercol) select 1,'Second'
SET @err = @@error
if @err <> 0
begin
print 'oops error found'
update test set othercol='if error'
end
Con you please check and see if both works or only the second code works?
Thanks
Dev
October 22, 2008 at 12:15 pm
in my case, i didn't see any differences:
connecting to either SQL 2000 or SQL 2005 servers, with SSMS,
when running the code, in both situations the error was found, and the update occurs when i select from the test table; it didn't matter whether i tested @@error, or the local @err variable.
I also changed the db compatibility level to 70/80/90, and in all three situations, they all executed the update in the IF block whether it tested @@error or the local variable....no difference;
Correct me if I'm wrong, but you are saying that if you do not capture the error in a local variable, the UPDATE block is not being executed, right?
Lowell
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply