troubleshooting Question

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

Avatar of KWDave
KWDaveFlag for United States of America asked on
SQL
2 Comments1 Solution507 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
knightEknight

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros