Hi . How use procedure add in sql server 2005 ?
create table Product
(
IDproduct char(10) primary key not null,
inventory int,
)
create table DetailBill
(
IDproduct char(10) not null,
foreign key(IDproduct) references Product(IDproduct)
balance int,
)
create proc AdddetailBill (@idproduct char(10),@balance int)
as
declare @number int
set @number=(select inventory from product where idproduct=@idproduct)
if @balance > @number
begin
print 'lack number of product'
return 0
end
else
begin
update product
set inventory=inventory - @balance
where @idproduct=(select idproduct from product)
insert detailbill(IDproduct,balance)values(@idproduct,@balance)
print 'Add success'
return 1
end
Example :
In table Product :
ID product Inventory
P001 ...
Go to the complete details ...