/************************
Simple Transaction
transactions can contain many related
SQL commands. Use when all must happen
or none
run each statment seperately.
***************************/

Begin tran

Update Customer
Set CustLastName='Zukofsky'
--look at the rows affected and realize your mistake

rollback tran

Select * from Customer

--correct your error and then run

Begin tran

Update Customer
Set CustLastName='Zukofsky'
Where CustID=8

Commit tran

/****************************
Simple stored procedure
subscription information
for a particular customer
******************************/
Create proc usp_CustomerSubscription
--declare the parameter that must be passed
--to the procedure
@CustID int
AS --start the procedure
--run the query using the parameter value
--as a criteria
Select c.custID, CustLastName, custFirstName, MagName, 
	subscriptionStart, Subscriptionend
From Customer c
Inner Join Subscription s
On c.CustID=s.custID
Inner join MagazineDetail md
On md.magDetId=s.magDetID
Inner Join Magazine m
On m.MagID=md.MagID
Where c.CustID=@CustID

--try the procedure
usp_CustomerSubscription 3

/**********************************
Alter the above so that you can enter the customer
last and first names rather than the 
Custid. this introduces variables
*************************************/
Alter proc usp_CustomerSubscription
--two parameters
@Lastname varchar(30),
@firstName varchar(25)
AS --start procedure
--Declare a variable
Declare @custID int
--use a select to assign a value
--to the variable
Select @CustID=CustID 
From customer
Where CustLastname=@lastname
And CustFirstName=@firstname
--now do the original query
Select c.custID, CustLastName, custFirstName, MagName, 
	subscriptionStart, Subscriptionend
From Customer c
Inner Join Subscription s
On c.CustID=s.custID
Inner join MagazineDetail md
On md.magDetId=s.magDetID
Inner Join Magazine m
On m.MagID=md.MagID
Where c.CustID=@CustID



/*******************************
finally add a try catch to handle errors
This actually doesn't add any functionality
in this case, but shows the syntax
*********************************/
Alter proc usp_CustomerSubscription
--two parameters
@Lastname varchar(30),
@firstName varchar(25)
AS --start procedure
--Declare a variable
Declare @custID int
--use a select to assign a value
--to the variable
Begin Try --start the try block
Select @CustID=CustID 
From customer
Where CustLastname=@lastname
And CustFirstName=@firstname
--now do the original query
Select c.custID, CustLastName, custFirstName, MagName, 
	subscriptionStart, Subscriptionend
From Customer c
Inner Join Subscription s
On c.CustID=s.custID
Inner join MagazineDetail md
On md.magDetId=s.magDetID
Inner Join Magazine m
On m.MagID=md.MagID
Where c.CustID=@CustID
End try --end the try block
Begin catch --begin the catch block
Print 'Enter a last and first name'
end Catch


/****************************
Simple stored procedure
Enter a new customer
******************************/

Create proc usp_newCustomer
--list parameters that must be
--passed to the procedure
@LastName varchar(30),
@firstname varchar(25),
@Address varchar(100),
@City varchar(50),
@state char(2),
@zip char(11),
@phone char(10)
AS --start the procedure
Insert into Customer(CustLastName, CustfirstName, CustAddress,
    CustCity, CustState, CustZipCode, CustPhone)
    Values(@LastName, @firstname,@Address,@City, @State,
		   @zip, @phone)

--test the procedure
usp_newCustomer
@Lastname='Anderson',
@firstname='Amy',
@Address='101 DeliverHere Ave',
@City='Renton',
@State='WA',
@zip='98100',
@phone='5326650022'

Select * from customer
Order by CustLastName

/*************************************
Now alter the above procedure so that it checks 
to see if the customer already exists
**************************************/

Alter proc usp_newCustomer
--list parameters that must be
--passed to the procedure
@LastName varchar(30),
@firstname varchar(25),
@Address varchar(100),
@City varchar(50),
@state char(2),
@zip char(11),
@phone char(10)
AS --start the procedure
--check for the existence of the customer
If exists
	(Select * from Customer
		where custLastName=@lastname
		And custfirstname=@firstname
		And custAddress=@Address
		And CustZipcode=@zip)
Begin --begin the true block
Print 'Customer already exists;'
Return --exit the stored procedure
end --end the true block
Else --otherwise
Begin --begin else block
--insert the customer
Insert into Customer(CustLastName, CustfirstName, CustAddress,
    CustCity, CustState, CustZipCode, CustPhone)
    Values(@LastName, @firstname,@Address,@City, @State,
		   @zip, @phone)
print 'Customer Inserted' --provide a message
End --end else block

--try with an existing customer

usp_newCustomer
@Lastname='Anderson',
@firstname='Amy',
@Address='101 DeliverHere Ave',
@City='Renton',
@State='WA',
@zip='98100',
@phone='5326650022'

--try with a new subscription
--Notice the alternate way to list the parameters

usp_newCustomer 'Yesler','Thomas','201 Nowhere Else blvd','Federal Way',
'WA', '98333','3604646677'

Select * from customer
Order by CustLastName Desc

--Alter the procedure to use the
--built in variable @@Identity

Alter proc usp_newCustomer
--list parameters that must be
--passed to the procedure
@LastName varchar(30),
@firstname varchar(25),
@Address varchar(100),
@City varchar(50),
@state char(2),
@zip char(11),
@phone char(10)
AS --start the procedure
--check for the existence of the customer
If exists
	(Select * from Customer
		where custLastName=@lastname
		And custfirstname=@firstname
		And custAddress=@Address
		And CustZipcode=@zip)
Begin --begin the true block
Print 'Customer already exists;'
Return --exit the stored procedure
end --end the true block
Else --otherwise
Begin --begin else block
--insert the customer
Insert into Customer(CustLastName, CustfirstName, CustAddress,
    CustCity, CustState, CustZipCode, CustPhone)
    Values(@LastName, @firstname,@Address,@City, @State,
		   @zip, @phone)
--@@Identity returns the last identity created
print 'Customer Inserted' + ' ' + Cast(@@Identity as char) --provide a message
End --end else block

usp_newCustomer 'Larson', 'Jill', '3241 ninth street',
	'Seattle','WA','98211','2064541011'

/**********************************************
Now to put everything together: stored prodcedure
transaction,parameters and variables
 @@identity and also a try carch block
Let's add a customer and a subscription
Here is the psuedocode:

	Get the customer information, 
		the magazine name 
		and length of subscription
	Declare @CustID
	Begin transaction
	Begin try
	Check to see if the customer exists
	If the Customer exists
	Get the customerID
	End block
	ELse (if customer doesn't exist
	Insert Customer information
	Get the new Customer identity
	End Else block
	Query to get the magId that goes with
		the magazine name
	Query the MagazineDetail table to
		get the magDetID that goes with
		the magazine and subscription type
	Insert the subscription information
	End Try
	Begin Catch
	If errors rollback transaction
	Provide a message to the user
	Return, exit procedure
	End catch
	Commit transaction

This one we will do together
************************************************/