Scalar User Defined Function (UDF) in SQL Server 2005

As I explained in my previous article that Scalar User Defined Function is return only one single value so I would like to convert one of my stored procedures I have developed for BSA (Body Surface Area) count into scalar UDF. Since it is returning only one value, we can develop Scalar function for that.

BTW, you can refer my basic article on UDF and SP of BSA with following URLs.

http://www.sqlhub.com/2009/03/user-defined-function-or-udf-in-sql.html

http://www.sqlhub.com/2009/03/bsa-body-surface-area-calculation-in.html

Now, let us create scalar value function to calculate BSA in Microsoft SQL Server 2005:

--CREATING scalar function to return BSA (Body Surface Area)

--The calculation is from the formula of DuBois and DuBois:

--BSA = (W 0.425 x H 0.725) x 0.007184

--where the weight is in kilograms and the height is in centimeters.

--

--

--DuBois D, DuBois EF. A formula to estimate the approximate surface area

--if height and weight be known. Arch Intern Medicine. 1916; 17:863-71.

--Wang Y, Moss J, Thisted R. Predictors of body surface area.

--J Clin Anesth. 1992; 4(1):4-10

CREATE FUNCTION dbo.CalculateBSA(@option INT,@weight FLOAT,@height FLOAT)

RETURNS FLOAT

AS

BEGIN

DECLARE @bsa FLOAT

--if weight and height are in kg and cm accordingly

IF @option=1

BEGIN

SET @bsa=power(@weight,0.425)*power(@height,0.725)*0.007184

END

--if weight and height are in lbs and inch accordigly

ELSE

BEGIN

SET @weight=(@weight/2.2046)

SET @height=@height*2.54

SET @bsa=power(@weight,0.425)*power(@height,0.725)*0.007184

END

RETURN @bsa

END

GO

--run CalculateBSA function

SELECT dbo.CalculateBSA(1,95,180)

Reference: Ritesh Shah
http://www.SQLHub.Com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com