helo all...,
i have create procedure can decreasetotalcost from order table(database:games.dbo) withbalance in bill table(database:bank.dbo). my 2 database in same server is name "boy"
i have 2 database like: bank.dbo and games.dbo
in games.dbo, have a table name is order(user_id,no_order,date,totalcost)
in bank.dbo, have a table name like is bill(no_bill,balance)
this is a list of bill table
no_bill balance
111222 200$
222444 10$
this is a list of order table
user_id no_order date totalcost
a 1 1/1/07 50$
when customer insert no_bill(111222) in page and click a button, then bill table became
no_bill balance
111222 150$
222444 10$
when customer insert no_bill(222444) in page and click a button, then message "sorry, your balance is not enough"
mystore procedure like:
ALTER PROCEDURE [dbo].[pay]
(
@.no_bill AS INT,
@.no_order AS int,
@.totalcost AS money
)
AS
BEGIN
BEGIN TRANSACTION
DECLARE @.balanc AS money
SET @.balanc= (SELECT [balance] FROM Bank.dbo.bill WHERE [no_bill] = @.no_bill)
UPDATE [bank.dbo.bill]
SET
[balance] = @.balanc - @.totalcost
WHERE
[no_bill] = @.no_bill
COMMIT TRANSACTION
END
it can decrease money in bank, but i want it ceck money if balance > totalcost, so balance-totalcost,
if balance<totalcost,so error message"sorry, your balance not enough"
is it can make in procedure?
thx...
Try this:
ALTER PROCEDURE [bank].[dbo].[pay]( @.no_billINT, @.no_orderint, @.totalcostmoney, @.messagevarchar(100)-- make it output parameter in your stored procedure)ASBEGIN TRANSACTION DECLARE @.balancAS moneyselect @.balance = balancefrom bank.dbo.billwhere no_bill = @.no_billselect @.totalcost = totalcostfrom games.dbo.totalcostwhere no_order = @.no_orderif (@.balance > @.totalcost)beginset @.balance = @.balance - @.totalcostUPDATE bank.dbo.billSET [balance] = @.balanceWHERE [no_bill] = @.no_bill-- set @.message = 'your have enough balance'endelsebeginset @.message ='sorry, your balance not enough'endCOMMIT TRANSACTIONset nocount off
Good luck.
|||thx...
ur code is not display message. when i execute ur store procedure, it display:
type direction name value
int in no_bill we insert to this,ex:110
int in no_order we insert to this,ex:2
money in totalcost we insert to this, ex 100$
char in message ??? if i not insert to this, my error :Procedure or Function 'pay' expects parameter '@.message', which was not supplied.
so it must to insert it, but my purpose is display message automatic. how can i change direction to be output?
can u add output code to stroreprocedure?
pls..,thx...
