transactions(Try, commit and rollback)

Question

transactions(Try, commit and rollback)

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
October 11, 2019 at 8:46 pm

#3688140
Ignore post
- This topic was modified 5 years, 1 month ago by mcfarlandparkway.

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

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

That really depends on what you are trying to do. If it was me, and just guessing based on a lot of the variable names and trying to eyeball that wall of text, I'd RETURN if the number of errors was greater than 0. If there is an error in the data, you likely don't want to keep going. But maybe you do?

I do not see a place where I would worry about a TRY from looking at that code as I don't see any places offhand (without knowing the source data structure that is) that would benefit from using a try. But tossing a transaction around the update and insert is probably a good idea; or maybe even around the entire thing?

If you did want to put a TRY in, I'd probably say to toss it in on the UPDATE and/or INSERT portions of the code and rollback on a catch... but again, it depends on what you are trying to do. There is nothing in there that NEEDS it to have a TRY, COMMIT or ROLLBACK.

There is a few things I'd change about the code (like being consistent... you have SET for some of the @total variables and SELECT for others).

Why/where are you thinking a TRY...CATCH block would be helpful?

Where are you thinking that you need to put a transaction around this?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Steve Collins SSCrazy Points: 2157 More actions · Answer 2

Could you provide the definitions for the tables that you reference? Is there an integer identity primary key on id in the student table?

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Deleting your post after others have taken the time to respond, and not even thanking them, is rather rude in my opinion.

So here is a copy of the original, as received by e-mail, in case others are interested:

Create procedure dbo.student
AS
BEGIN
SET NOCOUNT ON

DECLARE
@TotalUpdate BIGINT = 0,
@TotalInserted BIGINT = 0,
@TotalErrors INT = 0

create table #std(
id varchar (10) NULL,
StudentName nvarchar(50) NULL)

insert into #std
(
id
,StudentName)
select
id
,StudentName
from temp.student std
where std.isflag = 'Y'

insert into dbo.errortable
(
id
,StudentName)
select
id
,StudentName
from temp.student std2
where std2.id not in (select disitnct id from #std)
and not exists (select 1 from dbo.errortable er
where std2.id = er.id)

select @TotalErrors = @@Rowcount

update sd
set
id = tmp.ID
,StudentName = tmp.StudentName
,modifieddate = getdate()
from dbo.student sd
join #std tmp on sd.ID = tmp.ID

SET @TotalUpdate = @@RowCount

Insert into dbo.Student
(
id
,StudentName)
select
id
,StudentName
from #std std1
where not exists (select 1 from dbo.Student std2
where std1.ID = std2.ID)

SET @TotalInserted = @@RowCount

END

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin