How to Create a SQL Stored Procedure who give difference of two columns in a new column for each row

Question

Hello

     

    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.

OUTPUT LIKE

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 :)

With Regards

Rohit Sharma

Answer 1

Please any one reply me regarding this query.

Answer 2

Create Proc <Store Procedure Name>

as

Begin

Select bill, payment, (bill-payment) as new_balance from <tablename>

End

---

Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

Answer 3

DECLARE

@bill numeric(10,2) ,

DECLARE

@payment numeric(10,2)

DECLARE

@balance numeric(10,2)

SET

@balance = 0

--Say table name tblTransaction

DECLARE

db_cursor CURSOR FOR SELECT bill,payment FROM tblTransaction

OPEN

db_cursor

FETCH

NEXT FROM db_cursor INTO @bill,@payment

WHILE

@@FETCH_STATUS = 0

BEGIN

Set @balance

=@balance+(@bill-@payment)

/*

First balance will keep balance of current balance

Second balance will keep the balance amount of previous row

*/

Select @bill

END

,@payment,@balance                                                  NEXT FROM db_cursor INTO @bill,@payment

CLOSE

db_cursor

DEALLOCATE

db_cursor

---

Ahsan Kabir