How to reduce seating number - 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 2005 Forums

Transact-SQL (2005)

How to reduce seating number

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2010-04-14 : 11:04:32

Greetings all,

I am building a system to allows people to register online for our in-house training programs.

The total number of seats allowed per training session is 35.

This could go up or down.

Anytime a user registers for training, that number reduces by 1 until it is 0.

When it is zero and a user attempts to register, s/he is given a message that says, "No more seats available".

My big question/concern is how to come code it so that that number - 35 decrements with each registration.

I hope my need is clear. If not, please let me know and I can explain further.

Thanks a lot for your assistance.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-14 : 11:16:53

There are several ways. One, perhaps, more simple way is to perform your UPDATE and check the @@ROWCOUNT to see if a row was updated. Here is another way using the OUTPUT clause. The has the advantage of not being affected by triggers and such (like @@ROWCOUNT can be):

DECLARE @UpdateTrainingSessionID INT

BEGIN TRAN

	DECLARE @MyOut TABLE (TrainingSessionID INT)

	UPDATE 
		dbo.TrainingSession
	SET 
		SeatsAvail = SeatsAvail - 1
	OUTPUT
		Inserted.TrainingSessionID
	INTO
		@MyOut
	WHERE
		TrainingSessionID = @TraningSessionID -- Assuming this is passed to stored proc.
		AND SeatsAvail > 1 
		
	SELECT 
		@UpdateTrainingSessionID = TrainingSessionID
	FROM
		@MyOut
		
	IF @UpdateTrainingSessionID IS NULL
	BEGIN
		-- No Seats avilable
		-- Rollback and RaiseError or soemthing
	END
	-- Else continue to associate the User with a Training Session
	INSERT
		UserTrantingSession
		(
			UserID,
			TrainingSessionID
		)
	SELECT
		@UserID,
		@TraningSessionID
	
COMMIT TRANSACTION

For future reference, it's helpful if you can post your table strcuture (and sample data). Here is a link that helps explain what will help us to help you better:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2010-04-14 : 12:30:01

Thank you very much for your help.

For future reference, it's helpful if you can post your table strcuture (and sample data).

I will. Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources