/*Create the sample database */

Create Database testpurchase

Use testpurchase

-- create tables
Create table Inventory
(Item int identity(1,1) Not null,
Itemname varchar(20),
Numberinstock int)

Create table Purchase
(Purchaseid int identity(1,1) not null,
 Item int,
 Quantity int)

--add the referential integrity constraints

Alter table inventory
add constraint pk_item primary key(item)

Alter table Purchase
Add Constraint pk_Purchase primary key (purchaseid)

Alter table Purchase
Add constraint fk_item foreign key(item)
references Inventory

--insert a few sample records

Insert into Inventory(Itemname, numberinstock)
Values ('Microsoft windows', 10)

Insert into Inventory(Itemname, numberinstock)
Values ('Microsoft Word', 10)

Insert into Inventory(Itemname, numberinstock)
Values ('SQL Server', 10)

Insert into Inventory(Itemname, numberinstock)
Values ('Dreamweaver', 10)

/*create the first stored procedure
This procedure creates a transaction to 
insert into the purchase table and then
to update the inventory table. It uses 
the built in @@error variable to trap errors
and determine whether to rollback or commit */

Create procedure sp_Purchaseupdate
--declare parameters
@item int,
@Quantity int
--start the procedure
AS
/* declare the error variable
the @@error only remembers the last
SQL statement. This variable is meant 
to collect all errors */
Declare @err int

--begin a transaction
Begin tran

--insert the values from the paramaters
Insert into Purchase(Item, quantity)
Values (@item, @Quantity)
--Check for errors
Select @err = @@error
--print error number or 0
print @err

-- update inventory
Update Inventory
Set numberinstock=numberinstock+@quantity
Where item = @item
--Check for errors
Select @err = @err + @@error
Print @err

--Determine whether an error has occurred
If @err > 0
--begin block if true
Begin
rollback Tran
--end block if true
End
--otherwise
Else
--begin else block
Begin
Commit tran
--end else block
End

/*save the stored procedure by running it. 
If you need to correct errors do so
then rune the following */


--this will add 10 to dreamweaver
sp_purchaseupdate 4, 10

--this should create an error and rollback

sp_purchaseupdate 5,15


/*Here is a more advanced stored procedure
that checks to see if the item exists in
inventory. If it does it just adds the purchase
and updates the inventory; if not it inserts the
product into inventory and inserts a new purchase */

Create proc sp_newitem
--declare the parameters
@Itemname varchar(20),
@Quantity int
As
--declare the variables
Declare @item int, @err int

--check to see if the item exists
If exists
  (Select Itemname 
   From Inventory
   Where itemname=@itemname)

--begin the block for if it does exist
Begin
--start a transaction
Begin tran
--find the item number for the item
Select @item=item
From Inventory
where Itemname=@itemname
--Check for errors
Select @err=@@error

--insert into purchase
Insert into Purchase (item, quantity)
Values (@item, @quantity)
Select @err=@err + @@error

--Update inventory
Update Inventory
Set numberinstock=numberinstock+@quantity
where item=@item
Select @err=@err + @@error

--decide to commit or rollback
if @err>0
Begin
Rollback tran
End
Else
Begin
Commit Tran
End

--end block for if exists
End

--if it doesn't exist
Else
Begin
Begin Tran

--insert the new software into inventory
Insert into Inventory(itemname, numberinstock)
Values (@itemname, @quantity)
Select @err=@err + @@error

--Get the item number for the new software
Select @item=@@identity

--insert it into purchase
insert into purchase(item, quantity)
Values (@item, @quantity)
Select @err=@err + @@error

--determine commit or rollback
if @err>0
Begin
Rollback tran
End
Else
Begin
Commit Tran
End

--end else block
End


/* run to save. Correct any errors.
Try the lines below */

--Existing item
sp_newitem 'Dreamweaver', 10

--new items
sp_newitem 'Photoshop', 10
sp_newItem 'Flash', 10