BSA (Body Surface Area) calculation in stored procedure with OUTPUT parameter in Microsoft SQL Server 2005:

Herewith, I am giving you one more example of OUTPUT parameter in Stored Procedure for calculating BSA (Body Surface Area). You are in development of medical software than BSA is not a new term for you. If your body surface are is between 1 to 2 than it is normal as per my little knowledge about BSA. I am giving a sample example which will calculate BSA based on the given height and weight. Height and Weight should be either in kg (weight) and cm (height) or in lbs (weight) and inch (height). You can make it more customize by giving more dynamic conversions.

–CREATING stored procedure 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.

CREATE PROC CalcBSA

@option INT,

@weight FLOAT,

@height FLOAT,

@bsa FLOAT OUTPUT

AS

SET NOCOUNT ON

–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

GO

–once you done with creating stored procedure, let us see whether actually it works!!!!

DECLARE @BSA FLOAT

EXECUTE calcbsa 1,84,180,@BSA OUTPUT

PRINT @BSA

GO

If you are new to stored procedure and wants to study it than do have a look at my following basic articles. Those articles contain from basic definition of stored procedure to different usage of SP.

http://www.sqlhub.com/2009/03/stored-procedure-in-microsoft-sql.html

http://www.sqlhub.com/2009/03/return-data-with-output-parameter-from.html

http://www.sqlhub.com/2009/03/dml-insert-with-multiple-ways-in-sql.html

http://www.sqlhub.com/2009/03/delete-many-multiple-records-in-bunch.html

http://www.sqlhub.com/2009/03/startup-stored-procedure-sql-server.html

http://www.sqlhub.com/2009/03/create-your-own-system-stored-procedure.html

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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Return data with OUTPUT parameter from Stored Procedure in SQL Server 2005

Herewith, I am keeping my promise and moving ahead with topic of stored procedure. I will be explaining how to return data from stored procedure in SQL Server with OUTPUT parameter. If you are new to Stored Procedure than I kindly advice you to move to my prior article about stored procedure at:

http://www.sqlhub.com/2009/03/stored-procedure-in-microsoft-sql.html

When you have need to return data to the calling procedure, you should use of OUTPUT parameter of SQL Server in Stored Procedure.

If you are returning record set for single value, I strongly insist to use OUTPUT parameter as it is much much faster than returning the value.

–create one table for demo

use adventureworks

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)

–INSERT records

INSERT INTO emps

SELECT ‘Ritesh’,‘MIS’,‘echem’ UNION ALL

SELECT ‘Bihag’, ‘MIS’, ‘CT’ UNION ALL

SELECT ‘Rajan’, ‘account’,‘Marwadi’ UNION ALL

SELECT ‘Alka’,‘account’,‘tata’ UNION ALL

SELECT ‘Alpesh’,‘Chemical’,‘echem’

GO

–creating stored procedure which will return data with OUTPUT parameter

CREATE PROC getEmpDeptbyEmpName(@EmpName VARCHAR(50),@EmpDept VARCHAR(10) OUTPUT)

AS

SELECT @EmpDept=dept FROM emps WHERE Name=@EmpName

GO

–calling SP and catching return value in @EmpDept

DECLARE @EmpDept VARCHAR(50)

EXECUTE getEmpDeptbyEmpName ‘Ritesh’, @EmpDept OUTPUT

SELECT @EmpDept AS ‘Department’

GO

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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah