How do I handle default values?

Question

How do I handle default values?

simflex-897410

Hall of Fame

Points: 3881
More actions
June 1, 2013 at 2:48 pm

#278124

Greetings experts,
I have 2 fieldnames on 2 different tables.
One fieldname is called SeatCapacity. This fieldname name has a value of 45 and it is on a table called Locations
Then there is another field called RemainingSeats on a table called tblMain.
tblMain table contains details of signups such as loginId of who signed up, course signed up for, date of training, etc.
This fieldname should contain an initial value of 45 as well.
Each time an individual signs up for a seat, 1 is substracted from RemainingSeats.
For instance, if RemainingSeats is 45 and I sign up for a seat, RemainingSeats should only be 44.
This goes on till RemainingSeats is 0 in which case, no one can sign up anymore.
Here are my issues right now.
1, how do I assign initial value of 45 to RemainingSeats?
I tried adding 45 to tblMain in design mode in ssms but I can't see this value.
2, When I tried doing an insert statement like:
Insert into tblMain(loginId, dateofCourse, courseName,RemainingSeats) VALUES(1,'2/12/2012','Test Course', (RemainingSeats - 1))
When I try testing this, I get the following error:
The name "RemainingSeats" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
How do I resolve these?
Thanks a lot in advance

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

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

Sean Lange SSC Guru Points: 286575 More actions · Answer 1

The most straight forward way is to NOT store calculated data. You should just have to be calculated as you need it. AvailableSeats = InitialSeats - sum(RegisteredSeats).

The query you have will never work. You are doing an insert, during that insert RemainingSeats has no value. If you are deadset on storing calculated data you will have to a subquery. Storing data like that will come back to bite you. It is not a matter of if, it is a matter of when and how often.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/