Function or SP

I have this BMI calculation running in a query and was wondering if it would be better to use a function or SP or ? and if yes, how to go about doing that. I don't feel like this is the most efficient way to do it but I had to get something working in a pinch.

MAX(CASE WHEN p.HeightUnits = 'Cm' AND
                      p.WeightUnits = 'Kgs' THEN ROUND(CAST(p.weight AS float) / square(CAST(p.height AS float) / 100), 1) WHEN p.HeightUnits = 'In' AND
                      p.WeightUnits = 'Lbs' THEN ROUND(CAST(p.weight AS float) / square(CAST(p.height AS float)) * 703, 1) WHEN p.HeightUnits = 'FtIn' AND
                      p.WeightUnits = 'Lbs' THEN ROUND(CAST(p.weight AS float) / square(CAST(p.height AS float)) * 703, 1) WHEN p.HeightUnits = 'Cm' AND
                      p.WeightUnits = 'Lbs' THEN ROUND((CAST(p.weight AS float) * 0.453592) / square(CAST(p.height AS float) / 100), 1) WHEN p.HeightUnits = 'In' AND
                      p.WeightUnits = 'Kgs' THEN ROUND(CAST(p.weight AS float) / square(CAST(p.height AS float) * 0.0254), 1) WHEN p.HeightUnits = 'FtIn' AND
                      p.WeightUnits = 'Kgs' THEN ROUND(CAST(p.weight AS float) / square(CAST(p.height AS float) * 0.0254), 1) END) AS BMI
LVL 1
LCNWAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
From a purely performance/efficiency standpoint, inline code will be much better than either a function or a stored proc.

Although, for readability reasons, I prefer this formatting :-) :


MAX(CASE
    WHEN p.HeightUnits = 'Cm' AND  p.WeightUnits = 'Kgs' THEN
        ROUND(CAST(p.weight AS float) / SQUARE(CAST(p.height AS float) / 100), 1)
    WHEN p.HeightUnits = 'In' AND p.WeightUnits = 'Lbs' THEN
        ROUND(CAST(p.weight AS float) / SQUARE(CAST(p.height AS float)) * 703, 1)
    WHEN p.HeightUnits = 'FtIn' AND p.WeightUnits = 'Lbs' THEN
        ROUND(CAST(p.weight AS float) / SQUARE(CAST(p.height AS float)) * 703, 1)
    WHEN p.HeightUnits = 'Cm' AND p.WeightUnits = 'Lbs' THEN
        ROUND((CAST(p.weight AS float) * 0.453592) / SQUARE(CAST(p.height AS float) / 100), 1)
    WHEN p.HeightUnits = 'In' AND p.WeightUnits = 'Kgs' THEN
        ROUND(CAST(p.weight AS float) / SQUARE(CAST(p.height AS float) * 0.0254), 1)
    WHEN p.HeightUnits = 'FtIn' AND p.WeightUnits = 'Kgs' THEN
        ROUND(CAST(p.weight AS float) / SQUARE(CAST(p.height AS float) * 0.0254), 1)
    END) AS BMI
0
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
it totally depends on your need, functions (except the inline functions )are slower in performance, buy they can be called inside a select query.
While sps  are faster,  you cannot use them in a select query, but if moving everything inside an sp is possible, then i would go with sp. otherwise you need to decide, whether to go for  a function or a select statement with the above case statements
0
 
LCNWAuthor Commented:
Thanks for the suggestions.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.