need help to improve my stored procedure

Question

need help to improve my stored procedure

Qira

SSCommitted

Points: 1930
More actions
November 1, 2011 at 1:46 am

#247028

Hi,
I've stored procedure as following,
create proc assignprogram
@studentId int,
@programid int
as
declare @progquota int,@allotedcnt int,@nextprogram int
select @progquota =quota
from @t1
where programID=@programid
select @allotedcnt=count(*)
from @tResult
where programID=@programid
if @allotedcnt < @progquota
insert into @tResult (studentID, programID) values(@studentId,@programid)
else
begin
select top 1 @nextprogram= t.programID
from @t1 t
cross apply (select count(*) as cnt
from @tResult
where programID = t.programID
)t1
where cnt < t.quota
order by t.programID
if @nextprogram is not null
insert into @tResult (studentID, programID) values(@studentId,@nextprogram)
else
raiserror ('no program available at this moment',16,1)
end
go
How to make sure my transaction above apply ACID? It's atomicity, consistency, isolated and durability
My concern is once my trasaction is not finish yet, another transaction will spoilt my data integrity
need help

Viewing 8 posts - 1 through 7 (of 7 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You have no transaction in that code, and no error handling. Every statement runs independently and can succeed or fail by itself.

Read up on transactions, Try.. catch and isolation levels

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Qira SSCommitted Points: 1930 More actions · Answer 2

Hi Gail,

There are four isolation levels:

1. READ UNCOMMITTED

2. READ COMMITTED

3. REPEATABLE READ

4. SERIALIZABLE

which one is suitable and the best?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Suitable for your code, that only you can decide. Best - none of them. They all have advantages and disadvantages, you should understand them and be able to decide on the one necessary for your code.

You missed 2 - snapshot and read committed snapshot.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Oh, and for transactions and error handling, have a look at this: http://sqlinthewild.co.za/index.php/2011/05/17/on-transactions-errors-and-rollbacks/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Qira SSCommitted Points: 1930 More actions · Answer 5

Qira

SSCommitted

Points: 1930

November 5, 2011 at 6:38 am

#1403607

tq Gail

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 6

GilaMonster, excellent points and directions.. currently visiting the URL you have posted..

Cheers,
John Esraelo

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 7

Thank you for the link, Gail.

It's a very well written article about a very important subject.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA