http://www.sqlhub.com
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
@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
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
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),
CarName VARCHAR(16),
CarDesc VARCHAR(50)
)
–insert records
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’)
GO
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
where ID=1
GO
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’
WHERE ID=2
GO
Enjoy the power of Microsoft SQL Server 2005
Happy SQLing!!!!!
Reference: Ritesh Shah