Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
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
@bsa FLOAT OUTPUT
SET NOCOUNT ON
–if weight and height are in kg and cm accordingly
–if weight and height are in lbs and inch accordigly
–once you done with creating stored procedure, let us see whether actually it works!!!!
DECLARE @BSA FLOAT
EXECUTE calcbsa 1,84,180,@BSA OUTPUT
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.
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:
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
CREATE TABLE emps
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
–creating stored procedure which will return data with OUTPUT parameter
CREATE PROC getEmpDeptbyEmpName(@EmpName VARCHAR(50),@EmpDept VARCHAR(10) OUTPUT)
SELECT @EmpDept=dept FROM emps WHERE Name=@EmpName
–calling SP and catching return value in @EmpDept
DECLARE @EmpDept VARCHAR(50)
EXECUTE getEmpDeptbyEmpName ‘Ritesh’, @EmpDept OUTPUT
SELECT @EmpDept AS ‘Department’
You may remember pseudo table in trigger from which we can get manipulated data. There was no way out to access that pseudo table outside the trigger before Microsoft SQL Server 2005. The same concept you can get outside of trigger as well in Microsoft SQL Server 2005.
Let us create one table for demonstration
–create first table for demonstration
CREATE TABLE CAR
ID int IDENTITY(1,1),
Insert into Car (CarName,CarDesc) values (‘Honda Pilot’,‘SUV’)
Insert into Car values (‘Honda CRV’,‘SUV’)
Return data from INSERT statement with INSERTED table
Insert into Car
OUTPUT INSERTED.* –this statement will return all the field of CAR table
— from INSERTED table
values (‘Honda CRV’,‘SUV’)
Return data from DELETE statement WITH DELETED table
DELETE FROM CAR
OUTPUT DELETED.* –this statement will return all records
–which are just deleted based on where condition
Return data from UPDATE statement from INSERTED and DELETED table.
UPDATE CAR SET CarDesc=‘Luxury car’
OUTPUT DELETED.CarDesc as ‘Old Value’, INSERTED.CarDesc as ‘New Value’
Enjoy the power of Microsoft SQL Server 2005
Reference: Ritesh Shah