Computed column help, or another direction?

Question

Post reply

Computed column help, or another direction?

Jeff Sims-413169

Say Hey Kid

Points: 711
More actions
November 19, 2010 at 10:17 am

#388272

They want to build a DB for our pc naming which will automatically give the pc techs the next pcname to use when installing a new pc.

PC naming convention is PC (1-2) Region(3) Dept(4-7) and number(9-11) ex: pcdADM001.

I setup tables with Region table and Dept Table for pc tech to pick from.

Now setting up Pcname table with Region, DEPT, Number, pcname, and pcfullname

Pcname is computed column 'PC' + [region]+[Dept] so with example above would be PCdADM.

I then need to figure out how to get Number column to be Max(Number) + 1 so if add next pcdADM pc it will search for pcname = PcdADM and find the max value for number and add 1. So 002. Then I can compute pcfullname as pcname+number or PCdADM002.

Or would it just be easier to code that in the front end(ASP) so once pctech chooses from list fields SQL computes the pcname they can click on get next pcfullname and the code will do the search of the SQL and the number field and insert number into the SQL. Ok I think that is the way to go so might have answered my own question here.

Not looking for code write or anything just looking at advise or if anyone has done something like this and what would be the best route.

Thanks,

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

I'd compute it in the proc that inserts the record. Parameters for region and dept, then insert and return the full name. That'll be easier than a computed column.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Sean Lange SSC Guru Points: 286562 More actions · Answer 2

CELKO (11/19/2010)

...The un-issued serial numbers will have NULLs in those columns...

Wow Celko, I am surprised that you of all people would suggest to use NULL like this. In your proposed idea you would be using a NULL to indicate that it is not issued. I have a rule against using NULL to indicate the status of something. In a case like this I would add a bit field IsAssigned. 😉

_______________________________________________________________

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/

Jeff Sims-413169 Say Hey Kid Points: 711 More actions · Answer 3

I like the idea.. But the sure number of pc's not to include the number of possible regions, then departments within that regions would make it extremely time consuming to create the pcfullname in advance. Guess I could create a proc to populate by grabbing region + dept out of thier tables and then creating 100 or whatever pcfullnames + number I would want. Issue there is there would be a lot of unused rows as some departments would need only a couple numbers while some would require many.

Think I may just design the tables then have the front end code get the region and department from the pc tech and then have that search the table for MAX number with that pc(region)Dept) then add one and populate the Pcfullname column with result.

Thanks,

Jeff

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Jeff Sims-413169 (11/19/2010)

They want to build a DB for our pc naming which will automatically give the pc techs the next pcname to use when installing a new pc.

PC naming convention is PC (1-2) Region(3) Dept(4-7) and number(9-11) ex: pcdADM001.

I setup tables with Region table and Dept Table for pc tech to pick from.

Now setting up Pcname table with Region, DEPT, Number, pcname, and pcfullname

Pcname is computed column 'PC' + [region]+[Dept] so with example above would be PCdADM.

I then need to figure out how to get Number column to be Max(Number) + 1 so if add next pcdADM pc it will search for pcname = PcdADM and find the max value for number and add 1. So 002. Then I can compute pcfullname as pcname+number or PCdADM002.

Or would it just be easier to code that in the front end(ASP) so once pctech chooses from list fields SQL computes the pcname they can click on get next pcfullname and the code will do the search of the SQL and the number field and insert number into the SQL. Ok I think that is the way to go so might have answered my own question here.

Not looking for code write or anything just looking at advise or if anyone has done something like this and what would be the best route.

Thanks,

What do you want to happen when you exceed #999?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions