How to Create a SQL Stored Procedure who give difference of two columns in a new column for each row
Thursday, July 12, 2012 5:34 AM
I want to create a sql server procedure who subtract two columns and
give its result to the last new added column.Suppose i have two currency fields
in columns as 'bill','payment'.Now i want to calculate it in new column (not existing
in table or view) name balance as balance =balance+(bill-payment) for each row.
bill payment balance(new)
400 300 100
500 400 200
700 500 400
here balance work as balance=balance+(bill-payment) for each successive row.
Thanks in Advance for the help :)
Thursday, July 12, 2012 6:07 AMPlease any one reply me regarding this query.
Thursday, July 12, 2012 9:13 AM
Create Proc <Store Procedure Name>
Select bill, payment, (bill-payment) as new_balance from <tablename>
Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!
- Marked As Answer by Eileen ZhaoMicrosoft Contingent Staff, Moderator Wednesday, July 18, 2012 3:24 AM
Thursday, July 19, 2012 5:40 AM
DECLARE@bill numeric(10,2) ,
SET@balance = 0
--Say table name tblTransaction
DECLAREdb_cursor CURSOR FOR SELECT bill,payment FROM tblTransaction
FETCHNEXT FROM db_cursor INTO @bill,@payment
WHILE@@FETCH_STATUS = 0
First balance will keep balance of current balance
Second balance will keep the balance amount of previous row
END,@payment,@balance NEXT FROM db_cursor INTO @bill,@payment
- Edited by Ahsan Kabir Thursday, July 19, 2012 5:41 AM