Height column - 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

General SQL Server Forums

New to SQL Server Programming

Height column

Author

Topic

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2006-11-30 : 12:55:20

I need to fill a height column. I could just type it in, but I'd like to know how to do it programatically.

Columns
HeightID- (int Identity 12, 1) {minumum human height being arbitrarily 12 inches, incrementing by 1, so the ID is the actual height in inches. We need the low numbers beceause we might be dealing with infants}

Height- (varchar(10))as (HeightID/12)', (MOD(HeightID, 12))" with the quote characters and the space

Sample return:
HeightID Height
-----------------------
12 1' 0"
13 1' 1"
14 1' 2"
and so on, but stopping at 7' 5", or heightID = 89

I know it's some kind of WHILE, Insert, break if, continue loop, but I can't seem to do it, especially with the quote characters making life difficult.
Does that make sense?

Thanks in advance!

----------------
-Stephen

Kristen
Test

22859 Posts

Posted - 2006-11-30 : 13:14:44

Can't your HEIGHT column just be a computed column? Or a Function? Or a VIEW?

Not sure you want an IDENTITY, that just makes it harder to add values. Just use a TINYINT

If you want to insert 89 rows you can do something like


INSERT INTO MyTable(HeightID)
SELECT [MyNumber] = MyTens + MyUnits
FROM
(
   SELECT MyTens, MyUnits
   FROM
   (
       SELECT [MyTens] = 10 UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL SELECT 40 UNION ALL 
                            SELECT 50 UNION ALL SELECT 60 UNION ALL SELECT 70 UNION ALL SELECT 80
    ) AS TENS,
   (
       SELECT [MyUnits] = 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
                            UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 9
    ) AS UNITS
) AS X
WHERE MyTens + MyUnits BETWEEN 12 AND 89

Kristen

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-30 : 13:18:51

[code]declare @Height table (HeightID int)
declare @count int
set @count = 12
while @count < 90
begin
insert @Height values (@count)
set @count = @count + 1
end
select HeightID,
cast(HeightID / 12 as varchar(2)) + ''' '
+ cast(HeightID % 12 as varchar(2)) + '"' as Height
from @Height[/code]

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2006-11-30 : 13:46:18

Wow, thanks, SnSQL, that's exactly it!
Thanks Kristen, too.
I need both columns because it's a lookup, and babies' heights are specified in inches, but when toddlers reach about 3 feet, it switches. I don't want anyone to have to do the conversion in their head. So yes; a view/computed columns/function would work fine, too.

----------------
-Stephen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources