Stored procedure assignment

The assignment

Here is a clearer view of exactly what needs to be done

Create a schema called "Client"

All of the following procedures and views should belong to this client schema.

We will create a user for the client next week and set permissions

A view that shows the available services. Alias the fields

A simple stored procedure showing past grants based on person key

Status of current grant stored proc with grant number or personkey

Stored proc for new client

Store proc for new grant

Trigger on insert for lifetime limit of $2500

Optional function for total grant amount across individual grants

---

Here is the code for the procedures and triggers we did in class

/*
this stored procedure inserts a new 
customer 
*/

Alter proc usp_NewCustomer
--these are the parameters
@LastName nvarchar(255), 
@FirstName Nvarchar(255),
@LicenseNumber nvarchar(10),
@VehicleMake nvarchar(255), 
@VehicleYear nchar(4),
@Email nvarchar(255),
@CustomerPassword nvarchar(20)
As
Begin Transaction --begin the transaction
Begin try --begin try to try the code
--as soon as it encounters an error it will abort
--and go to the catch

--do inserts
Insert into Person(lastname, firstname)
values (@lastName, @firstname)

Declare @Personkey int
Set @Personkey=Ident_current('Person')

Insert into Customer.Vehicle(LicenseNumber, VehicleMake, VehicleYear, PersonKey)
Values(@LicenseNumber, @VehicleMake, @VehicleYear, @Personkey)


Insert into Customer.RegisteredCustomer(Email, CustomerPassword, PersonKey)
Values (@Email, @CustomerPassword, @PersonKey)
--if no errors commit the inserts
--won't get here if there is an error
Commit transaction
End try --end the try
Begin Catch --catch any errors
Rollback tran --rollback the transaction
print error_Message() --print the error
End catch

go
exec usp_NewCustomer
@LastName ='Rubble', 
@FirstName='Barney',
@LicenseNumber='12Stone',
@VehicleMake='boulder', 
@VehicleYear='0000',
@Email ='brubble@flinstone.com',
@CustomerPassword='bpass'

Select * from Person
Select * from Customer.Vehicle
Select * From customer.RegisteredCustomer

alter table Customer.RegisteredCustomer
add constraint unique_email unique(email)



Go
Create trigger tr_PoorRating on Customer.CustomerSurvey
after insert
As
Declare @Rating int
Select @Rating = rating from inserted
if @Rating < 3
Begin
 if not exists
  (Select name from sys.tables where name = 'LowRatings')
  Begin
   Create table LowRatings
   (
    LocationID int,
    SurveyDate datetime,
    Rating int,
    comments xml
   )
  end
  Insert into LowRatings(LocationID, SurveyDate, Rating, Comments)
  Select LocationId, SurveyDate, Rating, Comments from Inserted
end

Insert into Customer.CustomerSurvey(LocationID, SurveyDate, Rating, Comments)
Values(2, GetDate(), 1, null)

Select * from Customer.CustomerSurvey

Select * From LowRatings