MS SQL INT arithmatic to save result as varchar - always returns 0

KWDave used Ask the Experts™
I have values for height and weight stored in MS SQL 2008 as characters.

I need to calculate BMI, and store the result as varchar in the database.

The result always returns as 0. I tried declaring BMI as Float and Decimal and get the same result. I don't need the decimal part - the whole number result would be fine.

@BMI_NOTE is declared as VARCHAR(1000)
@Height and @Weight are declared as INT.

--Calculate BMI

SET @Height = CAST(@L1s3503 AS INT);
SET @Weight = CAST(@L1s3504 AS INT);

--Calculate BMI
--IF ((@L1s3504 IS NOT NULL) AND (@L1s3503 IS NOT NULL))
SET @BMI = ((@Weight/@Height/@Height) * 703) ;

--INSERT into NotesAnswers
SET @BMI_NOTE = 'BMI = ' + CAST(@BMI AS CHAR(4)) + ' Height = ' + CAST(@Height AS CHAR(4)) + ' Weight = ' + CAST(@Weight AS CHAR(4));
INSERT INTO NotesAnswer (Facility, Resident, InstrumentInstance, Note)
            VALUES(@Facility, @Resident, @InstrumentInstanceID, @BMI_NOTE);

The values for @Height and @weight are saved correct.

Thanks for any help
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Consider casting Height and Weight to float instead of int.  Then convert the result to varchar:

SET @BMI = convert( varchar, (@Weight/@Height/@Height) * 703) ;


Elegant and worked, with simpler syntax.

Thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial