make procedure to check balance > or < totalcost

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...