We help IT Professionals succeed at work.
Get Started

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

KWDave asked
Last Modified: 2012-08-13
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
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE