Update places available - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update places available

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2011-08-30 : 05:00:58

Hi I need to updata a table with a column which has the number of places available for a booking.
So I can use a @numberofplaces int and then need to have some check like

if PlacesAvailble >= @numberofplaces then
PlacesAvailable = PlacesAvailable - @NumberOfPlaces
return true
else
return false
end if

And I also think you need to lock the record for the duration of the update.
How can I do that?

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2011-08-30 : 05:17:17

So far I've got

ALTER PROCEDURE [dbo].[DemoDate_UpdateAvailable]
@DateID int,
@NumberOfSeats int
AS

Begin
if exists(select * from dbo.DemoDate where [NumberOfSeatsAvailable] >= @NumberOfSeats and DemoDateID = @DateID )
begin
update
dbo.DemoDate
set
[NumberOfSeatsAvailable] = [NumberOfSeatsAvailable] - @NumberOfSeats
where
DemoDateID = @DateID
select 1
end
else
select 0
End

The secret to creativity is knowing how to hide your sources. (Einstein)

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2011-08-30 : 05:17:50

So how can I lock the record

The secret to creativity is knowing how to hide your sources. (Einstein)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources